Excel Dirty Data
November 17, 2015
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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.
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!
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 27, 2019 1:55 PM