Rows and Columns

Introduction

• The focus of the drills is on inserting rows and columns in Excel.

Navigating to the Correct Sheet

• Switch to Sheet Two using the keyboard shortcut:

  • Control + Page Down from Sheet Three.

Inputting Data

• Input data in Rows 5 and 6 as per the example shown.

• Important:

  • Apply the same formatting as in the original exercise, using only the keyboard.

  • Ensure that the cells for Net Profit contain calculations, not inputs.

Adding a Row for Non-Operating Costs

• To add a new row below Operating Costs:

  • Navigate to any cell in Row 6 (the row below Operating Costs).

  • Two methods to insert a new row:

  1. Press Alt + I then R.

  2. Highlight Row 6 by pressing Shift + Space Bar, then press Control + Shift + Plus (+) to insert a new row.

• Input new data as shown below and modify the Net Profit calculation accordingly:

  • For cell editing, use F2 to enter edit mode.

  • Remember to format inputs with Paste Special Formats.

Adding a Column for 2004

• To add a column for the year 2004:

  • Go to any cell in Column C:

  • Use the shortcut Alt + I then C to insert the new column.

  • Alternatively, highlight Column C with Control + Space Bar and insert the column with Control + Shift + Plus (+).

• Insert and copy data:

  • Copy data using Control + C and paste using Control + V.

  • Formulas in Row 4 and Row 7 should be copied as well, while inputting the required manual data for blue cells as indicated.

Finalizing the Data and Formatting

• Return to Sheet Two using Control + Page Up and input the information from the screenshot:

  • Input Operating Costs and Net Profit after ensuring Net Profit remains a calculation.

  • Utilize the arrow keys for calculation input and use the Equal Sign to start the formula.

  • Use Shift + Right Arrow Key followed by Control + R to fill the formula across to the right.

Formatting the Sheet

• Format data correctly:

  • Use Control + 1 to open the format menu, navigate to Number tab and select 1,000 comma separator.

  • Paste special formats for cell coloring using Control + C, followed by Alt + E + S + T for pasting formats.

  • Set borders for cells:

  • Open border tab with Control + 1 and toggle until finding Outline, select it using the Space Bar.

• The preferred keystrokes for adding a new row below operating costs are:

  • Alt + I + R; alternatively Shift + Space Bar and Control + Shift + Plus (+).

Adjusting Net Profit Formula

• Input non-operating costs data (e.g., 15 across the board).

• Adjust the Net Profit formula:

  • Enter edit mode using F2, modify the formula to subtract non-operating costs.

  • If necessary to toggle back to arrow key navigation, press F2 again.

• To avoid formatting issues when copy-pasting:

  • Use Control + C then perform a Paste Special for formulas using Alt + E + S + F.

Adding 2004 Column again

• Reaffirm the method for adding the 2004 column:

  • Quick method: Alt + I + C to the left of the column intended for addition.

• Input data for 2004, and copy over necessary formulas/data, as mentioned previously.

Auto-Fitting and Deleting Rows/Columns

• Auto-fit columns using shortcut Alt + H + O + I.

• To delete rows and columns, use the following keys:

  • Alternate + H then D then R to delete a row.

  • Be aware that deleting rows referenced by formulas may result in a #REF! error.

  • To delete a column, use Alt + H + D + C.

Conclusion

• The drills cover important aspects of utilising Paste Special, focusing on pasting formats and formulas separately to maintain desired formatting.