How to create an Excel summary page? OK! What is in an Excel summary page? On the surface it’s a page that summarises data from other sheets in your Excel workbook. But if you dig a little deeper, it should be a whole lot more.
An Excel summary page should be the one stop shop for all the answers to questions you have about your data. If you want to know how much money you received throughout the entire year? An Excel summary sheet should show you.
Or maybe you would like a breakdown of exactly how much you spent on different expenses during the year. I mean, how much have you been spending on your lunches. It could be time to cut down on those lavish restaurant meals and start taking cheese sandwiches to work with you.
Either way, you need to know how much you’re spending before you can make any decision about what to do about it.
Handy Functions for an Excel Summary Page
OK, so hopefully you agree as to the merits of a summary page in Microsoft Excel. That being the case, how do you go above creating them. What are the functions that you should take advantage of?
SUM() Function in a Excel Summary Page
One of the most popular functions in Excel, and one that’s underrated. You see, the very fact that a summary page is called a summary page would seem to indicate that you would use the SUM function. So, knowing how to create a summary page in Excel would go hand in hand with the SUM function.
The basics of the SUM function are simple. It adds up a series of numbers and it works like this:
=SUM(Cells that you wish to add up)
So if I was to type in =SUM(A2:A16) the SUM function would add up all the numbers in cells A2 through to cell A16. If there were any cells that contained text data then they would be ignored. That’s the beauty of SUM, it just skips over any cells that contain numbers.
You can see from the above picture that the numbers between A2 to A16 add up to 87. The SUM function just ignores the text Nine and Two in the column. If, however, I was to change one of the text numbers to an actual number then SUM would include that number. As can be seen from below:
You can see that I’ve change the Nine to a 9 in cell A5. The result being that the SUM total has increase to 96. This is because the SUM function now recognises the number 9.
Understanding the nuances of the SUM function in an Excel summary sheet can make all the difference to the accuracy of your figures.
The SUMIF Function
Now that you have seen how the SUM function works in a summary sheet in Excel, it’s time to examine the SUMIF function.
So, what does the SUMIF function do that the SUM function doesn’t? Well, if you want a bit of a detailed explanation of the SUMIF function then you check out this SUMIF function page.
But, very quickly, the SUMIF function has 3 arguments as can be seen below.
=SUMIF(range to check, value to check against the range, range that you want to add)
range to check – This would be range in which the value to check would be in.
value to check – This is the value that the SUMIF function cross references
range that you want to add – This is the range of cells that you wish to add up should the range to check.
So if the range to check was a column of fruit and the value to check was Oranges, the SUMIF function would find all the oranges.
You can see an example of the SUMIF function above.
Excel Summary Sheet
If you take advantage of all the fantastic SUM functions that Excel provides, you can create a pretty decent summary sheet.
Would you rather see a video presentation? Then check out this Excel summary sheet video tutorial on the Computer Tutoring website.