Search Contextures Sites
Excel - How dirty is your data?
Nov 17, 2015
Create a rolling total, clean your data, chart tricks, and much more, in this week's Excel news.
- Debra - firstname.lastname@example.org
Create a Rolling Total
Recently, someone asked how to show a rolling total for the previous 12 months, instead of an ongoing running total. I set up the test data with dates in column A, sorted by date. Amounts were in column B, and this formula went into cell C2:
=SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2)
The formula compares dates to the date in the current row, and includes it in the total if it's in the past 12 months. You can read more about it, and download the sample file here.
Custom Footer Macro
If you create a custom footer for an Excel worksheet, and add the date, it uses the short date format, and you can't change it. To get more control over the footer appearance, you can use a macro to do the job.
With a bit of code, you can copy information from the worksheet, such as a customer name and order number, and put that on one side of the footer. Then, on the other side, add the date, formatted the way you like it.
Download my sample file, and watch the video, to see how to add this to your own files.
Here are a couple of Excel articles I read recently, that you might find useful.
Clean Your Data -- The worst part of working with data is getting it cleaned up. It might only take you 2 minutes to build a pivot table, but 4 hours to prepare the data! There are data cleanup techniques, along with other tips for data analysts, on the Investintech blog. (Level - Intermediate)
Chart Tips -- On his latest podcast, Chandoo talked about creating animated charts. To get some great ideas, listen to the podcast, and download the sample file. And remember that Jon Peltier's Chart Utilities can save you time too. (Level - Intermediate)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets.
15 Year Anniversary
This month is the 15 year anniversary for my Contextures website -- time sure flies when you're having fun with Excel! Coincidentally, this is also my 15th year of getting Microsoft's MVP award for my contributions in the Excel community.
Thank you for reading my newsletter and website, and keeping me motivated to continue. I appreciate it!
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: http://www.contextures.com/newsletter/excelnews2015/20151117ctx.html
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2015
All rights reserved.