Contextures

Contextures News 2020-08-11

Excel Date Problems

August 11, 2020

Date problems, pivot table group slicer, 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.

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.

Slicer Groups

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.

slicers for value groups

Date Problems

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.

gene codes changed to dates

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:

  • When typing, format the cell as Text first, or type an apostrophe before the value
  • When opening a csv file, choose a data type for each column, in Step 3 of the Text Import Wizard

choose a data type

P.S.: The old Text Import Wizard isn't on the Excel Ribbon now, but you can change an option to see it again.

Excel Dashboards

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.

Mynda offers full courses too, and they're 20% off, from August 11-20. Check out her Excel dashboard courses, and her Power BI courses.

Excel Articles

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)

Also see: My Excel Products || Previous Issues

New Laptop

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!

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

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.

contextures newsletter info

 

Last updated: August 21, 2020 4:19 PM