Excel Date Problems
August 11, 2020
Thank you for reading the news, and you'll get the next email in two weeks, on August 25th
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Pivot table slicers make it easy to filter the data, so you can focus on a specific region, or product, or time period. And here's a different way to use slicers -- show data groups in the Values area. This is helpful if your data has lots of numeric fields, and you only want to see a few at a time.
In the workbook, macros run after you click one of the slicers -- Group or Function. In the screen shot below, the travel group numbers are showing, with the Max function for each value.
To see how this technique works, and to download the sample file, go to the Pivot Table Slicer for Value Groups page on my Contextures site.
Have you run into this problem before? In Excel, if you type something that looks like a date ( 7/15 or 8-26), Excel "helps" you by converting that value to a real date.
Excel provides the same type of help if you open a csv format file in Excel, and that caused problems for some types of research data. For example, there is a gene named DEC2, and Excel converts that to December 2nd in the current year.
Now, after years of problems, the official gene-naming committee is changing those names, to avoid codes that could be interpreted as a date.
You can easily avoid those data problems though, with these steps:
P.S.: The old Text Import Wizard isn't on the Excel Ribbon now, but you can change an option to see it again.
Dashboards: Mynda Treacy has a new Plan vs Actual Excel dashboard tutorial. There's a step-by-step video, and you can download the sample workbook, to follow along.
Here are a couple of Excel-related articles that you might find useful or interesting.
Programming: If you'd like to get started with Excel programming, check out this free 3-part course from the University of Colorado Boulder - Excel/VBA for Creative Problem Solving. (Level - Programming)
Resources: There's a helpful collection of Excel learning material on the Dalhousie University library site. You'll find workbooks for beginners and experts, and exercises for working with data in Excel. (Level - All)
Last week, I got a new laptop (an HP Envy 17, similar to this one), and it took most of the week to set everything up. The first challenge was finding the On button -- it's in the top row of the keyboard, above the backspace key.
Fortunately, when I got a new laptop in 2016, I kept detailed notes, and wrote a blog post about the setup steps. There's another post with my Excel installation notes too. This time, I remembered to export my Ribbon customizations, and imported them on the new laptop. There are great tips in the comments for those blog posts too!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200811ctx.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: April 4, 2021 8:58 PM