Contextures News 20150818

Excel Chart Tricks

August 18, 2015

Chart tricks, time entry, pivot table layouts, 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.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Summer Schedule

During the summer months I'll publish the newsletter once every two weeks, so you will get the next issue on August 31st.

Time Entry Worksheet

For a time entry worksheet, you can prevent invalid time entries with drop down lists of hours and minutes. This technique uses data validation, with formulas, and dynamic ranges, but no macros.

To get the detailed instructions, and to download the sample file, go to the Excel Data Validation - Select Hours and Minutes page on my Contextures website.

Just remember, as with any data validation, someone could still find a way to enter invalid times. You could put other checks in the worksheet, such as conditional formatting, to alert you if invalid data was entered.

time entry drop downs

Pivot Table Layout

When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has all the row fields in a single column, with each field slightly indented from the previous field.

I prefer Outline or Tabular Layout, because they put each row field in a separate column, and show the field names. In the screen shot below, you can a pivot table with Compact Layout compared to Outline Layout.

You can manually change the layout, or use a macro, if you have lots of pivot tables to change. There are instructions on my Pivot Table blog.

pivot table layout options

Pivot Tables Survey

In the latest collection of Excel tweets, on my Excel Theatre blog, someone said, "How do I make a pivot table? I don't have time to try a bunch of stuff and see what works!"

Do you use pivot tables? Do you love them or hate them? Click here to answer my 3-question Excel Version survey, and I'll share the results in the next newsletter. Thanks!

Excel Articles

Here are a couple of Excel articles I read recently, that you might find interesting and useful.

Chart Tricks -- I found a couple of articles with chart tricks this week. Jon Peltier explains how to plot values along the axis of a chart, in a single line, similar to the markings on a thermometer. For a different take on line charts, Stephanie Evergreen shows how to create a slopegraph, to show how values change over time. (Level - Intermediate)

Excel Problems -- Excel is a great application, but things can go horribly wrong, as I'm sure you know. After working with a nightmare workbook, Melissa P. Esquibel shared the biggest lessons that she learned from the experience. (Level - Basic)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

If you're on Twitter, you can follow me there too. If you just want Excel information, follow my Contextures account, and for Excel mixed with a bit of other news, follow my Debra Dalgleish account.

Robot On Vacation

Last month, the Canadian hitchhiking robot was in the news, after it came to an untimely end while travelling through the USA. When my grandson came to visit this month, he created his own robot, out of supplies he found on our art table. Then, he put a fancy sheet of paper behind the robot, and sent it on a tropical vacation! So far, we haven't had any reports of trouble.

weekly photo

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

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.


Last updated: May 24, 2021 8:29 PM