Contextures News

Excel Rolling Total Solution

January 24, 2017

Solution to the Excel rolling total challenge, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Save Your Excel Ribbon

While experimenting with macros last week, I accidentally cleared all the buttons from my Quick Access Toolbar. D'oh! It took a while to rebuild it -- the hard part was remembering which commands used to be there.

I use those QAT commands all day long, and don't want to go through that rebuild process again. Fortunately, you can export your Ribbon customizations, and import them later, if you need them.

• Right-click the Ribbon, then click Customize the Ribbon
• At the bottom right, click the Import/Export button, for Customizations.
• Click Export All Customizations, and save the file in a folder where you can find it later.
• Click OK to close the Excel Options window.

And if you'd like to learn how to create custom tabs for the Ribbon, there's an introduction on my website.

Solution - Rolling Total Challenge

Thanks for participating in last week's Rolling Total challenge. The goal was to calculate a rolling total for up to 12 prior months, using the same formula in 24 columns of data.

As always, it's fun to see the many different approaches to an Excel problem. My solution uses the SUM and OFFSET functions, to create a range of 12 cells (start 12 or fewer cells to the left, include up to 12 cells), and get a total. It's a bit clunky, but it works!

=SUM(OFFSET(B7,0,-MIN(COUNT(A3:$B3),12),1,MIN(COUNT(A3:$B3),12)))

Other people had better solutions, and you can see them in the workbook. The shortest formula was this one, from ScottH: =SUMIF(A$3:$B3,">="&EOMONTH(A3,-11),A$7:$B7)

You can download the updated sample file, to see the original challenge and the solutions. Visit my website to learn more about the Date functions.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Power BI - If you're getting started in Power BI, Matt Allington explains how to do time-based comparisons, such as year to date, or rolling 12 months. (Level - Intermediate/Advanced)

Excel Contest - Mike Alexander is running a fun contest, the Excel 5 Minute Challenge. How would you pull together a quick report in Excel? Even if you're not entering, you can vote for your favourites in the contest playlist on YouTube. (Level - All)

Excel Events - See a list of upcoming Excel courses and free webinars on my Events page

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets. For example, "Excel is both the love and bane of my existence".

It's All a Blur

We've had lots of rain recently, which is better than snow, and much better than freezing rain. I took this photo at a stop light (from the passenger seat), while the rain was pouring down, and the windshield wipers could barely keep up. There are no filters or special effects on the photo, just raindrops. I'm glad we didn't have too much further to drive!

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

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

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.