Contextures News 20151117

Excel Dirty Data

November 17, 2015

Rolling total, custom footer, clean data, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

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.

Excel Articles

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!

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.




Newsletter Index



More Info

Free Excel Files

Excel Tips

Excel Products



About Debra







Last updated: November 27, 2019 1:55 PM