This fictional credit card data can give us a lot of information about our spending habits. Because the balance isn't paid off each month, the individual statements (January, February, and March) are connected. We can use information from one month to help analyze data in other months.
Available Files
The following files are available for download:
Copying a worksheet
Currently, our fictional credit card statements are in two different workbooks, MarchStatement.xlsx and Monthly_Statements.xlsx. Before we begin analyzing the data, let's copy the MarchStatement worksheet into the Monthly_Statments workbook.
- Open MarchStatement.xlsx, if necessary.
- To open the Move or Copy dialog box, at the bottom of the workbook,
Right-Click the MarchStatement tab, Click Move or Copy...
The Move or Copy dialog box opens:
By default, this dialog box prompts us to move the worksheet. We can choose where to move it: the current workbook, another open workbook, or a new workbook. We are also given the choice of where to move the worksheet: before the current worksheet or at the end of the workbook. At the bottom of the dialog box is a checkbox where we can indicate that we want to make a copy of the workbook.
- To make a copy of the workbook, in the lower left of the Move or Copy dialog box,
Click the Create a copy checkbox
- To begin placing the copy in the Monthly_Statements workbook, in the Before Sheet section,
Click
, Click Monthly_Statements.xlsx - To place the copied worksheet at the end of the Monthly_Statements workbook,
Click (move to end), Click
There is no visible change to the MarchStatement workbook. However, when we move to the Monthly_Statements workbook, we will see a copy of the March_Statement worksheet.
- To move to the Monthly_Statements workbook, on the View tab,
Click
, Click Monthly_Statements
The Monthly_Statements workbook is now active.
Rearranging worksheets
The MarchStatement worksheet was placed at the end of the row of worksheet tabs. However, it would make sense for this to go after the February worksheet tab.
We could use the Move or Copy dialog box to place this worksheet where we want it. But let's try a different method. We'll Press & drag the worksheet tab to the correct location.
When we Press & drag a worksheet tab, a small paper icon appears indicating where the workbook will be moved when we release the mouse.
- To move the MarchStatement worksheet,
Press & Drag the MarchStatement worksheet tab to the left until the icon and arrow are between the February and Spending tabs
The worksheet tabs are now in the correct order:
Before we move on, let's rename the MarchStatement worksheet so that it better matches the other months.
- To select the current name,
Double-Click the MarchStatement worksheet tab
- To rename the worksheet, type:
March Enter key
Referencing cells in another worksheet
The January and February worksheets both have a Balance column. This column calculates the balance as charges and payments are made. The beginning balance of each month is the ending balance of the previous month. Let's add a Balance column to the March worksheet and use February's ending balance as a starting point.
- Move to the March worksheet, if necessary.
- To add the Balance heading,
Click in cell H1, type: Balance Enter key
With the Balance heading in place, we are ready to reference a cell in the February worksheet as the previous balance.
- To begin referencing the cell, type:
=
- To move to the February worksheet, at the bottom of the workbook,
Click the February tab
- To reference the correct cell, in the February worksheet,
Click cell H109
- To accept H109 as the correct cell and return to the March worksheet, press:
Enter key
The February balance, 1836.05, appears in cell H2.
Let's take a moment to look at the formula in cell H2.
- To return to cell H2, on the keyboard, press:
Up Arrow key
The formula in cell H2 is:
=February!H109
The name of the worksheet being referenced, February, is followed by an exclamation point. The data after the exclamation point is the cell reference.
Let's format column H as currency.
- Move to the Home tab, if necessary.
- To remove the bold formatting from cell H2, on the Home tab,
Click
- To select column H, in the header row,
Click
- To format column H as currency, on the Home tab,
Click
, Click Currency
With the previous balance in place, we can now create a formula that will calculate the balance with each purchase and payment. To do this, we will use the closest balance amount which will be in the row above the selected cell. We will put this formula in cell H3 and copy it down the rest of the column.
- Make cell H3 the active cell.
- To begin the formula and reference the closest occurrence of the balance, type:
=h2
- To add purchases to the formula, type:
+f3
- To add the negative payments, type:
+g3
The formula looks like this: =h2+f3+g3
- To accept the formula, press:
Enter key
- To return to cell H3, on the keyboard, press:
Up Arrow key
- To copy the formula down column H,
Double-Click
- To widen column H, in the header row,
Double-Click between column H and column I
- Deselect column H.
Referencing an entire column
The workbook contains a fourth worksheet named Spending. In this worksheet, we track the spending for each month. The spending is calculated by adding up all of the purchases for the month. This could be done by using the SUM function and referencing all of the cells that contain a purchase. For example: =SUM(March!F3:F115). However, each month will have a different number of purchases and different cell references. In addition, typing or selecting the cells leaves room for error. To simplify this, we can reference an entire column.
Referencing an entire column is done by typing the column letter, a colon, and the column letter a second time. For example, =SUM(March!F:F). This function will add together all of the values contained in column F.
Let's move to the Spending worksheet and add the March spending total.
- To move to the Spending worksheet, at the bottom of the workbook,
Click the Spending tab
- To see the function in cell B1,
Click in cell B1
The function to calculate the January spending is =SUM(January!F:F).
- Make cell B3 the active cell.
- To begin entering the SUM function, type:
=sum(
- To enter the worksheet reference, type:
March!
- To enter the reference to the entire column, type:
F:F)
- To enter the function, on the keyboard, press:
Enter key
The total spending for March, $4,855.98, appears in cell B3.
- To save the workbook, on the keyboard, press:
Control key + S