Top Excel tips from Andy Pope, MVP at Digitab

In 2014, Andy Pope, Programming Manager at Digitab, was once again presented with the award of Excel MVP from Microsoft.

MVPs are passionate about Microsoft technology and recognized for their exceptional technical expertise. Of the more than 100 million social and technical community members worldwide, each year only 3,800 are recognized as MVPs and only 130 in Excel.

Here are a few of Andy's top Excel tips to help you get more from the software, save valuable time and create charts with increased ease. We hope you find them useful.

Keyboard Shortcuts

  • F4 key can be used to toggle through absolute and relative cell references when editing a formula.
  • To alternate between displaying results or cell formula on the worksheet you can use CTRL+.
  • To quickly create a chart from the data in the current region use F11. If you want the chart as an object on the worksheet use ALT+F1 instead.
  • To make working with worksheet objects, such as shapes or charts, easier use ALT+F10 to toggle display of the Selection task pane.
  • If you have macros in your workbook you can quickly swap between the VBE and workbook by using ALT+F11.

Charts

  • Bar charts populate category axis from the bottom upwards, in effect reversing the labels compared to their order on the worksheet. To make the order in the chart comparable to worksheet, double click vertical axis and set Categories in Reverse and Axis crosses at Maximum.
  • Hide zero value data labels in charts by using custom number format General;General;; If the data source for your category labels contains dates it may produce unexpected spacing on the axis. Set axis to Text to have dates treated as simple categories.

At Digitab, Andy and his team are available to provide you support and help on MS Office automation, dashboard and charting.

For more information or to speak to one of our consultants please call Mike Wright on tel +44 (0)20 7031 0287 or email mike@digitab.uk.com