Archive | July, 2013

Tame your computer – look under the table!

29 Jul

If you use Excel as a database (or rather ‘list’ as it is referred to in Excel) I’m pretty sure you love pivot tables, as they can help you to quickly retrieve and manipulate your worksheet data in numerous ways. But what I often see is that people use PivotTable reports as a simple, static summary, yet they can be used to interactively analyse the underlying data.

For example, you can quickly display the data ‘behind’ any figure in your report. Obviously, you can use the standard filter functionalities in the source data. But all you need is two clicks.

Here’s how:

  1. Double-click any of the values in the PivotTable report.

A new worksheet is created, displaying the related records from the source data. If you are already using version 2013 the new Quick Analysis button will appear next to the extracted information, allowing you to quickly and easily view your data with conditional formatting (colours and icons), sparklines, or charts.

With special thanks to my accountant – who was asking me for some additional information – for the inspiration for this week’s tip!

PS I’m committed to sharing the best of what I know with others so please don’t keep these tips a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit or reprint it (with full copyright and subscription information) in your newsletters and message boards. Got a tip to share? Submit it by emailing – if your tip is selected, I’ll publish it along with your name; the bragging rights will last a lifetime.

Tame your computer – lose the lines from links

17 Jul

A hyperlink is text or a graphic that points to another item — for example, a file on your hard disk or a Web page on either the World Wide Web or your organisation’s intranet.  Clicking on a hyperlink allows you to “jump” to the associated item.

The text hyperlink is typically underlined, but during a recent training session I was asked how to remove the underline in PowerPoint. The short answer is “you cannot”, but there is a workaround.

Here’s how:

  1. Drag and draw a shape where you want the hyperlink to appear on your slide.
  2. Right-click the shape, click Edit Text, and then type the text that you want to use for the hyperlink.
  3. Right-click the shape (but not the text inside of the shape) and select Hyperlink.
  4. In the Address box, type, say, the Web address, and then click OK.
  5. Right-click the shape and click Format Shape. In the Format Shape dialogue box, do the following:
  • Click Fill in the left pane, click Color in the right pane, and then click the color that matches the background of your presentation.
  • Click Line Color in the left pane, click No line in the right pane, and then click Close.
  • If necessary, right-click the shape and select a different Font Color in the mini toolbar above the list of menu options.

To test the hyperlink, in Slide Show view (F5 or SHIFT+F5 anyone?), click the hyperlink text (i.e. the “invisible” shape, containing the text).