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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!