Excel Chart Tricks
August 18, 2015
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
During the summer months I'll publish the newsletter once every two weeks, so you will get the next issue on August 31st.
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.
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.
NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings. That makes it easy to apply all your favourite settings to a new pivot table, or any existing pivot tables.
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!
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.
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.
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: https://www.contextures.com/newsletter/excelnews2015/20150818ctx.html
That's it for this week! If you have any comments or questions, send me an email.
Last updated: December 27, 2019 12:03 PM