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 @
     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.

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:

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




Last updated: May 24, 2021 7:32 PM