Contextures

Contextures News

Excel Challenge Rolling Total

January 17, 2017

Try 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.

Grouped Dates

By default, when you turn on an AutoFilter, the dates are grouped by year and month in the drop down list. If you'd rather see the individual dates, you can change one of the Excel options

To turn off the date grouping in the active workbook, follow these steps, or watch my video:

  • On the Ribbon, click the File tab, then click Options
  • Click the Advanced category
  • Scroll down to the Display Options for This Workbook section
  • Remove the check mark from Group Dates in the AutoFilter menu.
  • Click OK to apply the setting change.

NOTE: If you have my Excel Tools add-in, click the Workbook Appearance command, then click Toggle Filter Grouping.

group dates option

Excel Rolling Total Challenge

Our last Excel challenge was fun, so here's a new one. With 24 months of financial data, across 24 columns, create a formula that will show a rolling total for prior months. Include a maximum of 12 months, and use the same formula in all 24 columns.

You can download the sample file for the challenge, and work on a solution. For inspiration, there are videos and links on my Functions page, and several Sum functions here.

I'll show you my solution in next week's newsletter.

rolling total challenge

Excel Articles

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

Excel AddIns - Excel MVP Michael Olafusi put together a helpful list of resources, if you'd like to learn how to build one of the new type of Excel Add-in. These add-ins can be used on Windows, Mac or mobile versions of Excel. (Level - Intermediate/Advanced)

Spreadsheet Threats - Maybe it's the cold medication, but this list of spreadsheet threats made me laugh. Warning - There's some mildly offensive language. (Level - All)

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets. For example, "I've got excel files older than some of the people I work with".

Winter Cooking

Last week, I made stew using one of my old cookbooks. You can tell which recipes are the favourites, based on the number of spatters on the page. I added a few of the frozen leftover wine cubes from Christmas, and it was delicious. Leftover wine is a rare item at my house ;-)

weekly photo

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.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: March 15, 2017 11:56 AM