Excel Summary Sheet

An Excel Summary Sheet, what are we talking about?

An Excel summary sheet, why would you want to use one? How would you go about creating one? If you've asked any of these questions then you've come to the right place. Here you will learn how to create a summary sheet in Excel, the easy way. That's right! We plan to keep the formula complexity down to a minimum with the aim of you fully understanding the procedure.

What is a Summary Sheet?

This might seem pretty straightforward, but a summary sheet serves the purpose of summarising information that would otherwise be difficult to understand. You could be dealing with months of accounts data and the last thing you want to be doing is looking through every transaction.

Why an Excel Summary Sheet?

It's most likely that you'll want to know how much money you have earned from certain clients. Or review how much you have spent on lunch, fuel or mobile data over the past year. Understanding this can help you get better discounts when you shop around for a better deal next year.

What to include on the summary sheet?

Now that you know what a summary sheet is and why you should use it, the next question is: "What to include on the summary sheet?". Dates are always a good idea, especially the standard date components. How much money per week, month and quarter. As mentioned above, seeing how much you have spent on certain items throughout the year can help you make better decisions about the future.

How to Create an Excel Summary Sheet

Let's quickly move on to how you would go about creating a summary sheet in Microsoft Excel. In this tutorial you'll take an Excel workbook with data on separate sheets and summarise the data in those sheets on a single summary page. This tutorial is targeted at beginners with the purpose of teaching users of Excel to easily create a summary sheet.

The first thing you'll need to do is to download the exercise file:

Excel Summary Sheet Exercise File - Download

Now that you've downloaded and opened the file you can see the following sheet.

As you can see, the exercise file has a sheet called Summary. This is the sheet on which we will place the summary information.

1. Select on the Summary sheet.
2. In cell A1 Enter: Total.
3. In cell A3 enter: Receipts and in cell A4 enter Expenses.
   (Your sheet should look like)
   
   
4. Click in cell B3 and click the AutoSum button.
   
5. Now, using the mouse, click on the Apr tab hold down the shift key on the keyboard then click on the Mar tab. Release the shift key then click on the header of column I.
   
6. Now, press Enter.
   (You can view the result.)
7. Looking good! Next do the same for receipts. Do the same as you did before but this time click on column H. The formula should read:
   
   =SUM(Apr:Mar!H:H)
   
   Select cells B3 and B4 and hold Ctrl & Shift & $ to format the cells as currency.
   
   

The last think we need to do is to work out the profit. As the expenses are negative numbers all we need to do is to sum cells B3 and B4.

1. In Cell A4 enter Profit.
2. Click in cell B5 then use the AutoSum shortcut Alt & =.
3. Now press Enter.

Summarise Accounts Categories in Excel

Now you know how to summarise the receipts, expenses and therefore calculate your yearly profit. The next thing we're going to do is to summarise the individual expense and receipts categories. If you click on any month you will see a category column. In this column you can see all the expenses and receipts broken down into various categories. What we need to do is find out how much we spent on each category and how much we received from client payments.

The way that we'll accomplish this is this to first use the either the UNIQUE funciton or the Remove Duplicates feature to extract unique values from the categories list.

After than we will use the SUMIF function to calculate the exact values for each category. Don't worry if this sounds complicated, it's really pretty straight-forward.

The UNIQUE Function

If you are using Office 365 you can take advantage of the UNIQUE function. This is a great function if you have it available to you. If, however, you find that the UNIQUE function is unavailable then please proceed to the Remove Duplicates section below.

1. Click on the Apr sheet.
2. Now click Cell N1.
3. Enter the formula =UNIQUE(.
4. Now click on cell F1.
5. Using the keyboard hold down Ctrl & Shift & the Down Arrow, to select cells F1 to F170. The formula should read as follows:
   =UNIQUE(F1:F170)
6. Press Enter.
   (Now you can see a list of unique values.)
   
   
   (Before we carry on, we have to copy that list and paste the values. This is because we are going to replace the unique function with static values. We are bascially doing this to create a stability in the data. Also, if you are working with an older version of Excel and had to proceded to the remove duplicates method, I won't have to write two complete different sets of instructions... And I want to thank you for that.)
   
7. Select Cells N1 to N8.
8. Hold Ctrl & C to copy the cells.
9. Right click on the selection and click paste values.
   

Now you can see that you have UNIQUE values. These values are now no longer formulas, they are static values. Pasting values is extremely handy and is often used to protect formulas or to preserve consistency.

Removing Duplicates in Excel

I did say that the UNIQUE function was one way of removing duplicates in Excel. But the problem is that the UNIQUE function is only available to Office 365 users. The latest versions of Excel. What if, however, you are using an older version of Excel? In that case you will have to use another solution. The Remove Duplicates solution.

If you haven't got the UNIQUE function available to you do the following to remove the duplicates:

1. Select Cells F1 to F170.
   (Click Cell F1 then hold down Ctrl & Shift & the Down arrow on the keyboard to quickly select the cells.)
2. Press Ctrl & C to copy the cells.
3. Click in Cell N1 then press Ctrl & V to paste.
   
   
4. Click Data then Remove Duplicates.
   
5. In the Remove Duplicates box ensure the My data has headers box is ticked. Then Click OK.
   
6. Note that 162 duplicates were found and removed. Click OK.
   
   

Great! Now we are ready to use the SUMIF function to discover how much we've spent on certain things and how much money we made from client payments.

Using the SUMIF Function in Excel

Well done on coming this far. Hopefully, what you've learnt so far you can apply to your own accounts.

Next, we are going to use the SUMIF function to find out how much we have spent on different items throughout the year.

1. Click in Cell O1 and enter: Total.
2. In cell O2 enter the following formula:
   
   =SUMIF(F:F,N2,I:I)
3. Press Enter to see the result.
   
   
   
4. Next, because we are going to AutoFill the formula down to cell O8, we now need to add another sumif to take care of column H. Add the following to the above formula. The extra bit is added in bold.
   
   =SUMIF(F:F,N2,I:I) + SUMIF(F:F,N2,H:H)
   
5. Press
6. Press enter,then select O2 again and double click the AutoFill square.
   
   
7. Now press Ctrl & Shift & $ on the keyboard to format the cells as currency.
   
   

So now we have created a small summary sheet. We've created the summary on the April tab. Before we calculate the yearly totals we need to copy these totals across the other sheets. That is from May through to March.

You may think that you need to do this one sheet at a time. This would be laborious, time consuming and, yes, there is a faster way of doing this. We are going to select the sheets then paste the data from Apr sheet cells N1 to O8 to sheets May to Mar. Let's do this.

Enter data on multiple sheets at the same time

1. Select and copy cells N1 to 08 from the Apr sheet.
2. Select sheets May to Mar by click on May then holding the shift key and clicking on Mar.
3. Click cell N1 then click paste by using Ctrl & V.
   
   
4. Now click on each of the sheets and see that the formulas have been copied across to all of them.
5. Now, click on the Summary sheet and enter the following values:
   
   
6. Add the word Total in Cell B8 then in B9 enter the following formula.
   (Alt + = for AutoSum click Apr, then hold down the shift key and click Mar. Then click cell O2.)
   
7. Press Enter.
8. Now AutoFill down. Then view your results. Remember to Format the cells as currency by holding down Ctrl & Shift & $.

There you have the basics of creating an Excel summary sheet. Of course you might want to include other information. Also, there are better ways of creating the formulas, but this is the simplest way I know. The most complicated formula here is SUMIF.

The issue with SUMIF, however, is that it won't work across multiple sheets. That's why in the above Excel tutorial we've had to place the various expenses on separate pages. But what if you don't want to do that. What if you want to write a formula that will take care of that in one fell swoop. That's what we'll be looking at next time so make sure that you subscribe.

Other Excel Accounts Tutorials

Super Easy Accounts in Excel?

How to Make Your Spreadsheet Look Good?

Calculate VAT in Excel

Excel VAT Formula

Money Made Last Month

Pivot Tables for Beginners

Excel Financial Year Formula

Add Calculated Field to Pivot Table

Pivot Table Conditional Formatting

Delete Blank Rows in Excel

SUMIF Excel Multiple Sheets

Clean up Messy Excel Data