To begin to analyze this data, it may be necessary to split it in to smaller pieces, in this case, quarters. By moving each quarter to its own worksheet, it will be possible to analyze spending and income trends for each quarter as well as the whole year.

## Available Files

The following files are available for download:

- To select the Quarter 1 data,

Press & Drag cells A2:H54

- To copy the data, press:

Control key + C

- To add a new worksheet,

Click

- To paste the data in the new worksheet,

Click in cell A1, press: Control key + V

- To deselect the data,

Click in a blank area of the worksheet

- To select the default sheet name,

Double-click SheetX

- To rename the worksheet, type:

Report_Q1 Enter key

There are some things to be formatted on the Report_Q1 worksheet: columns aren't wide enough and there are no column headers. Don't take time to correct this now. Once all of the quarters are separated, the worksheets can be formatted simultaneously.

- To return to the Financial_Report worksheet,

Click the Financial_Report tab

The Quarter 1 data is still selected and the "marching ants" are still visible.

- To deselect the Quarter 1 data,

Click in any cell

- To remove the "marching ants," press:

Escape key

- To select the Quarter 2 data,

Press & Drag cells A55:H106

- To copy the data, press:

Control key + C

- To add a new worksheet,

Click

- To paste the data in the new worksheet,

Click in cell A1, press: Control key + V

- To deselect the data,

Click in a blank area of the worksheet

Now that the data has been copied, take time to renamed the new worksheet.

- To select the default sheet name,

Double-click SheetX

- To rename the worksheet: type,

Report_Q2 Enter key

This time not only are the columns too narrow and the column headers missing, there is an error running through column G (the Balance column). This #REF! error was caused by copying and pasting the formula in the Balance column without including a previous balance in the first row.

### Correcting the #REF! error

A #REF! error occurs when a formula references a cell that no longer exists or for some other reason is no longer valid. In this worksheet, the formula used to calculate the current balance relies on the previous balance. Because the formula was copied into the new worksheet, there is no previous balance. With a little bit of work, this can be corrected.

Take a moment to look at the formula in cell G1.

- To select cell G1, in the Report_Q2 worksheet,

Click in cell G1

The formula bar reads, =#REF!-E1+F1. The original balance formula refers back to the previous balance. When the formula was copied and pasted, the previous balance was left behind in the Financial_Report worksheet. This can be corrected by linking the previous balance to the correct cell.

- To select #REF! in the formula, in the formula bar,

Press & Drag #REF!

- To move to the Q1 worksheet,

Click the Report_Q1 worksheet tab

- To replace #REF!, in the Q1 worksheet,

Click cell G53, Press: Enter key

The balance in cell G1, $94,418.79, is now correct. In this case, copying the formula throughout column G will not give the correct results. The previous balance will continue to point to the Report_Q1 sheet. Therefore, beginning in cell G2, the previous balance needs to be corrected. This can be done by setting the previous balance to one row above.

- Select cell G2.
- To select the previous balance reference, in the formula bar,

Press & Drag, #REF!

- To replace the previous balance reference, type:

G1 Enter key

The calculated balance, $92,768.79, is now correct and can the formula can be copied throughout the column.

- To select the original formula,

Click in cell G2

- To copy the formula down column G,

Double-click the Autofill handle

### Challenge exercise: completing the balance columns for quarter 3 and quarter 4

Using what you have learned in previous steps, copy, paste, and correct the balance formula for quarters 3 and 4.

**NOTE**: In case you need some hints, the steps are described below.

- To select the Quarter 3 data,

Press & Drag cells A107:H160

- To copy the data, press:

Control key + C

- To add a new worksheet,

Click

- To paste the data in the new worksheet,

Click in cell A1, press: Control key + V

- To deselect the data,

Click in a blank area of the worksheet

Now let's rename the new worksheet.

- To select the default sheet name, on the worksheet tab,

Double-click SheetX

- To rename the worksheet: type,

Report_Q3 Enter key

While the steps for correcting the #REF! error in the Q3 worksheet are essentially the same as earlier, you'll want to copy the previous balance from the Q2 worksheet, not Q1.

- To select #REF! in the formula, in the formula bar,

Press & Drag #REF!

- To move to the Q2 worksheet,

Click the Report_Q2 worksheet tab

- To replace #REF!, in the Q2 worksheet,

Click cell G52, Press: Enter key

The balance in cell G1 is now correct. The previous balance will continue to point to the Report_Q2 sheet. Therefore, beginning in cell G2, the previous balance needs to be corrected. This can be done by setting the previous balance to one row above.

- To select the previous balance reference, in the formula bar,

Press & Drag #REF!

- To replace the previous balance reference, type:

G1 Enter key

The calculated balance is now correct and can the formula can be copied throughout the column.

- To select the original formula,

Click in cell G2

- To copy the formula down column G,

Double-click the Autofill handle

- To select the Quarter 4 data,

Press & Drag cells A161:H213

- To copy the data, press:

Control key + C

- To add a new worksheet,

Click

- To paste the data in the new worksheet,

Click in cell A1, press: Control key + V

- To deselect the data,

Click in a blank area of the worksheet

Now let's rename the new worksheet.

- To select the default sheet name,

Double-click SheetX

- To rename the worksheet: type,

Report_Q4 Enter key

While the steps for correcting the #REF! error in the Q4 worksheet are essentially the same as earlier, you'll want to copy the previous balance from the Q3 worksheet.

- To select #REF! in the formula, in the formula bar,

Press & Drag #REF!

- To move to the Q3 worksheet,

Click the Report_Q3 worksheet tab

- To replace #REF!, in the Q3 worksheet,

Click cell G55, Press: Enter key

The balance in cell G1 is now correct. The previous balance will continue to point to the Report_Q3 sheet. Therefore, beginning in cell G2, the previous balance needs to be corrected. This can be done by setting the previous balance to one row above.

- To select the previous balance reference, in the formula bar,

Press & Drag #REF!

- To replace the previous balance reference, type:

G1 Enter key

The calculated balance is now correct and can the formula can be copied throughout the column.

- To select the original formula,

Click in cell G2

- To copy the formula down column G,

Double-click the Autofill handle

- Save the workbook.