Compare Annual Data in Excel
November 12, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Has this ever happened to you? You need to make a new Excel file, so you open an old file that's similar to what you need. Your plan is to make a few changes, and then do a "Save As", to create the new file.
You delete a sheet, and make other changes, and then a co-worker interrupts you for a few minutes. When you get back to your workbook, you accidentally hit "Save", instead of "Save As", and the old file is destroyed! If you're lucky, you have a backup, or IT can retrieve a copy for you, from yesterday's network backup.
To prevent that problem, start with a copy of the old file. If you worked on that file recently:
Excel creates an unsaved copy of that file, with a number at the end of its name.
If the file isn't in the Recent list:
There are more Excel Workbook Tips on my Contextures website.
If you have a couple of years of daily data in Excel, you can use a pivot chart to quickly compare that data, month by month, year over year. There are detailed steps and a video on my Contextures blog, but the key step is to group the dates.
If you add a date field to the pivot table layout, Excel might automatically group the dates for you, and show the totals per year (Excel 2016 and later). To see the monthly totals too,
Then, move the Years field into the Column area, and you'll be able to compare annual data.
NOTE: If Excel is automatically grouping the pivot table dates, you can turn that setting off:
There are more pivot table grouping tips on my Contextures site.
Here are a few Excel-related articles that you might find useful or interesting.
Calendar: On Jon Wittwer's Vertex42 site, you can download an Excel "Random Acts of Kindness" calendar. You can see how all the formulas work, and change the items in the list, or add your own. You could even change it to a "Random Excel skills to learn" list! (Level - All)
Shortcuts: On the Daily Dose of Excel blog, Dick Kusleika shows how to use a keyboard shortcut to open Excel, if you have its icon on your Windows Taskbar. It only works for icons 1 to 9 though. (Level-All)
Reminder: Mynda Treacy shows how to make impressive interactive dashboards, using built-in Excel features, in her one-hour free webinar. Mynda offers full dashboard courses too - get 20% off if you register by Thursday, Nov. 14th. Click here for Excel Dashboard Course or Power BI Course info. Don't miss out - this is your last chance to attend this year (and this decade!)
Excel Tweets: See what people tweeted about Excel this week. How often do you watch Excel go into a death spiral?
A friend came to visit last week, and he brought a hand-carved wooden chess board for our grandson. He bought the chess set in Malawi, in the 1980s, when he lived in Africa for a few years. And with all the snow that we just got, this is a perfect time to stay indoors and play games!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191112ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 11, 2019 4:37 PM