Search Contextures Sites
Custom Search

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

Excel UserForms for Data Entry

 

30 Excel Functions in 30 Days

 

 

Contextures News

Your Excel Tips Newsletter from Contextures

August 18, 2015

Chart tricks, time entry, and much more, in this week's Excel news.

 -  Debra -  ddalgleish@contextures.com

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.

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.

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.

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.

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: http://www.contextures.com/newsletter/excelnews2015/20150818ctx.html

_____________

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

__

 

Privacy Policy

 

Contextures Inc., Copyright ©2015
All rights reserved.