Use Running Total to Compare Years
Apr 26, 2016
Quick running totals to compare years, fast filter tip, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
If you're comparing data from year to year, a running total can show the rate of growth as each year progresses. Instead of just seeing each month compared to that month in the previous year, see the overall total at each point in the year.
The quickest way to create a running total is with a pivot table -- right-click on a value, click Show Values As, and click Running Total In. There are instructions and a short step-by-step video on my website.
In the screen shot below, there are two copies of the same pivot table. The top one is showing the monthly totals, and the bottom one show a running total. You could have both charts on a dashboard, to show different views of the same data.
If there are lots of items in a list, it can be hard to find what you need, even with Excel's helpful filter tool. The filter lists the items in alphabetical order, but you might be looking for all the items that contain specific text -- not at the start of the item name.
This example has a short list, but I'd like to show all the rows were Tennis was selected as a sport. To make the filtering easy, start typing in the search box, instead of scrolling through the long list, and checking each item as you spot it (and you'll probably miss some!) I've given this tip to several people who bought my Data Validation Multi Select Premium (DVMSP) kit, and weren't sure how to filter the data after making multiple selections.
The filter list will automatically reduce the number of items, showing only those that contain the text that you've typed. That search box is a handy tool, and it's easy to overlook, if you're used to older versions, where you clicked on the items that you wanted. There are more AutoFilter tips on my website.
Here are a couple of Excel articles I read recently, that you might find useful.
Excel Tips -- On Chandoo's blog, Hui posted a list of favourite tips for working in Excel. I use tips 4, 6 and 13 all the time. Are any of your favourites in that list? If not, what would you add? (Level - All)
Accessible Spreadsheets -- If you're designing Excel files for other people to use, keep accessibility in mind. Microsoft has added an Accessibility Checker to Office 2016, and if you're using an earlier version, follow the suggestions on their updated Accessible Spreadsheet Checklist. I wish they'd take their own advice, and use a larger font in those in-cell drop down lists! (Level - All)
More Excel Articles -- Visit my Excel website for many more tutorials and videos.
I'm not too good at growing indoor plants, but the outdoor plants seem to flourish, despite my black thumb. One of the earliest to sprout every spring is this clump of rhubarb. I planted it about 15 years ago, and it comes back strong every year. A week ago, there were just a couple of shoots peeking out of the ground, and it grew exponentially this week!
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: June 14, 2017 11:48 PM