Contextures

Contextures News

Annoying Pivot Table Problems

Apr 12, 2016

Avoid pivot table time problems, get my product updates, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Pivot Table Problems

If you show time values in a pivot table, the total amounts might not look right. The times might be rounded, and the totals might be way too low. There are easy fixes for both of those pivot table time problems, and the details and a sample file are on the Pivot Table Time Values page on my Contextures site. The quick fixes are below.

  • For times that don't add up correctly, format the field with the [h]:mm number format, which totals the hours
  • For times that show tenths of a second as zero, add another field in the source data, linked to the original time field. Format the new field as General, add it to the pivot table, and format to show the tenths of a second: h:mm:ss.0

Have you ever run into these problem, or other annoying things about pivot tables?

Drag and Drop Hyperlinks

In a big workbook, it's handy to have hyperlinks that take you from one sheet to another. A quick way to create a hyperlink is by dragging and dropping. There is a very short video on my website that shows how to drag a cell, and drop it as a hyperlink on another sheet, to make a quick table of contents.

If you have trouble dragging to a different sheet, here's another way to do it:

  • Select the cell that you want to link to, point to its border, and press the right mouse button.
  • Drag the cell to a nearby blank cell.
  • Release the right mouse button, and click Create Hyperlink
  • Cut the hyperlink, and paste it onto another sheet

Thre is an animated gif on my site (below the video), that shows those steps.

Product Updates

I've made minor updates to my Dashboard Tools (ver 1.01) and Excel Tools (ver 1.7), to fix the Fill Blanks code.

To check your version, on the Ribbon tab, click the Contact Contextures command. To get the new version, use your original download link, or email me if you can't find your link.

Excel Articles

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

Custom Formats -- Conditional formatting is an easy way to highlight cells, but it is volatile and can slow down your workbook. Instead, Bill Jelen shows how to use custom number formatting. His examples show how you can set font colours, and add text to highlight the good or bad values. (Level - Basic)

Excel Groups -- You can find online help in Excel forums, such as the Microsoft forum, or Mr. Excel's site. There aren't too many offline groups where people talk about Excel, but if you're in the Wellington, NZ area, there are a couple that you can join. Jeff Weir gives the details on the Daily Dose of Excel blog. (Level - All)

More Excel Articles -- Visit my Excel website for many more tutorials and videos.

Waiting for the Doctor

We've got pretty good medical coverage here in Canada, but you can still face some long waits at the doctor's office. For example, there was quite a crowd at the veterinary clinic that opened in our living room last weekend. It took a while, but the patients were all very patient, and the doctor eventually treated everyone, free of charge!

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:

www.contextures.com/newsletter/excelnews2016/20160412ctx.html

Recommended Excel Products

  • Pivot Table Builder: Use the Pivot Table Builder add-in to quickly create or update pivot tables. You can store multiple lists with pivot table layout settings in your workbook. Quickly create and store those lists, then apply them to any existing pivot table in the workbook, or create a new pivot table.

________________________

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

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:48 PM