The EZ guide to the EZ guide
The first EZ guide article on number formatting was published just over a year ago. Since then there have been 11 further guides including a six guide series on PivotTables. In case you have missed any of them, here is an overview of what has been covered so far.
As well as setting out a custom number format to show negative numbers in brackets and red, and to use a dash for zeros, the number formatting guide looked at ways of making a particular number format easy to apply:
Content seriesView full content series
- Attach your chosen number format to the Comma Style button in the Number group of the Home Ribbon tab so that it can be applied to selected cells with a single click.
- Create a default Excel template named Book.xltx, located in the XLSTART folder so that the number format is automatically available in new workbooks.
- Create a macro to allocate your chosen number format to the Comma Style so that it can be added to any existing workbook with a single click
This guide covered the benefits of using Excel Tables and included step-by-step instructions for creating, naming and using Tables. The principal benefit was claimed to be the ability of references to Table columns to expand automatically to include new rows as they are added.
The other benefits of using Tables were also set out:
- Formulae and formats can be copied automatically to the other rows in the same column and will extend to new rows added to the Table
- Filter and sort dropdowns are automatically added to the Table headings row
- Tables can be given meaningful names which are used as part of the structured formula language available to create references to Table contents
- Slicers can be attached to Tables (from Excel 2013 onwards) to allow Tables to be filtered in the same way as PivotTables
- Tables can be used to quickly remove duplicates from a list
The news was not all positive however, the inability to use worksheet protection in conjunction with Tables was also highlighted as were issues with copying sheets and sharing workbooks containing Tables.
Three key Table tips provided the finale:
- Using Control+r to copy structured references as absolute rather than relative.
- Using reference AutoComplete to make it quicker and easier to refer to Table elements.
- Adding the Table Name text box to the Quick Access Toolbar.
This guide used the example of the VAT rate to illustrate how the use of Range Names can make Excel formulae easier to construct and to understand.
As well as converting =B23*Data!B3 to =B3*VATrate other benefits of using Range Names were covered:
- No need to add $ to references to fix the reference when copied;
- Access to the Range Name from Excel AutoComplete when entering a formula;
- More robust links to cells in external workbooks;
- The ability to use the Name Manager to change the ranges to which names refer;
- The ability to overcome some Excel Table shortcomings.
Advanced features covered included the ability to allocate a formula directly to a Range Name without needing to enter it into a cell. The use of Range Names to allow functions to be used in formulae where they would not otherwise be valid, and the use of name intersections.
The guide ended with a warning about the dangers of copying whole sheets in which Range Names are used.
Industry insightsView more
This guide uses the advanced Range Name techniques covered previously to create a set of Sparklines that could be controlled dynamically:
The first task of this guide was to justify its existence. After all, cell references are such a basic foundation of Excel that many users will assume there is nothing further to learn about them. Accordingly, the guide showed just how important it was to fully understand how to use the dollar sign to fix individual elements of a cell reference, in order to ensure that your spreadsheet contains as few different formulae as possible. In so doing, the guide promised to make you at least 30 times more productive.
Spreadsheets can work really well until someone decides to enter the wrong type of value into an important cell. This guide described how Data Validation can help ensure that this doesn't happen. As well as covering the main elements of Data Validation, the importance of Error Alert Styles, and some of the different types of validation, the guide addressed some more advanced issues:
- Using the Go To dialog to find cells with Data Validation
- Copying Data Validation settings
- Using Data Validation lists to improve ease-of-use as well as data integrity
- Using Custom Validation settings
- Using Data Validation as an auditing technique
We kicked off a series of guides on PivotTables by seeking to address the Marmite issue (other breakfast spreads are available). Although the vast majority of accountants see PivotTables as the most exciting thing to have happened to human civilisation since opposable thumbs, there are others who just don't see the point. In a bid to help people decide whether PivotTables really did have a key role to play in their lives, the guide started with some publicly available data on UK house prices and showed that PivotTables could be simple as well as powerful, using just 17 mouse clicks to create a Top Ten league table of expensive regions:
The comments that the guide attracted showed that it was a long way from achieving its objective.
Having concentrated on persuasion in the first PivotTable guide, part two used the same house price data to look in more detail at how PivotTables work. In particular, each of the four PivotTable areas were examined in turn:
- Row area
- Column area
For PivotTable aficionados, a feature added in Excel 2010 was very much the slicing on the cake. After considering some of the basic interactive PivotTable features including click and drag, dropdowns and drill downs, this guide concentrated on the power of Slicers.
Slicers were introduced in Excel 2010 and provided a much more visual method of applying filters to a PivotTable, particularly when multiple items were selected. However, their real power lies in the ability to connect a single Slicer to multiple PivotTables allowing PivotTables to be used as the data engine behind an interactive dashboard without the need to do delve into the mysteries of macros and VBA code. Excel 2013 saw the introduction of a specific type of Slicer to work with data and time fields – the Timeline Slicer.
The guide introduced Slicers as a prequel to the excitement that lay ahead…
Just a few guides ago we were looking at how simple PivotTables could be. By part four we were using the techniques learnt, and in particular the Slicer expertise from Part three, to construct our own interactive dashboard, still using the house price data that we had started with at the beginning.
The guide explored the use of PivotCharts as a means of being able to display changing amounts of data within a fixed space and showed how to create several different PivotCharts based on the same source data before moving them to a single dashboard worksheet. Our Slicers were added and connected to all our source PivotTables so that they could control all the charts with a single click:
Having covered the basics of PivotTables from data acquisition to interactive dashboard, the next two parts looked in more detail at specific PivotTable features. In the guide to grouping we first looked at how to group dates so that we could work with months, quarters and years as well as individual days before looking at less obvious uses of grouping such as grouping values into bins and creating our own ad-hoc groupings. We finished by looking at the new chart grouping option buttons introduced in Excel 2016.
Before taking a summer break, our PivotTable guides revealed the many dark secrets of the values area including calculating Distinct Counts (e.g. how many different clients bought a particular product) and adding running totals.
We rounded off the initial set of EZ guides with help on rounding. Although the guide covered a variety of different rounding functions and techniques, including using Power Query/Get & Transform to round multiple values, the key focus was on warning the world that the 'Precision as displayed' Excel option is to danger what slugs are to slime.
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.