Excel Techniques
Although formulas are a core part of any good spreadsheet model, to create a complete model you need to employ other aspects of Excel to provide a good looking, informative and efficient model.. The diagram below lists some of the more important ones:
- Pivot Tables – An easy way to summarise large quantities of data into flexible tables and charts. Once created the pivot table quickly amended by drag and drop components. It can also be filtered using slicers. See in-depth (masterclass)section on Pivot tables
- Tables – Excel Tables (previously known as excel list) are easily created from the Insert ribbon. In addition to automatic totalling they offer easy formatting and filtering options. Handy features of Tables are automatic column fill for formulas and it is automatically given a name range which updates as you add more rows
- Range names – a way of giving a text name to a particular cell or range of cells.
- The Formula ribbon provides options for defining and managing range names
- Range names are useful if you need to refer to something several times in a spreadsheet e.g. The exchange rate between two currencies can be labelled ‘exchange’ (or anything else you wish) and then used in formulas such as =$b19*exchange (which will convert from currency A into Currency B).
- A range name can be dynamic i.e. automatically caries with the size of the data. See blog xx.xx
- Data Tools – The data ribbon provides several valuable tools
- Data Validation – see Blog xx.xx
- Remove duplicates – to create a list of unique vales. Often used as an intermediate step towards creating a succinct summary.
- Sorting – the ability to sort one or more columns of data into ascending or descending order
- Filtering – The ability to select only particular results to view. This flexible tool provides a number of criteria options e.g. exact match, Greater than, not equal too etc.
- Conditional format – a way of highlighting specific values in your spreadsheet.
- The values to be highlighted are based on criteria entered by the user e.g. exact match, less than, not equal to, etc
- An interesting feature is the ability to highlight based on a formula e.g. highlighting a whole row in your data if the final value is negative.
- Highlighting is done by means of formatting with many options available including cell colouring, font formatting, Symbols, traffic lights, and bars based on cell values.
- Viewing tools – in addition to the options to view your spreadsheet in normal, page layout or print view there are other useful options including
- Show options – create a nicer appearance for your output pages by switching off one or more of gridlines (I recommend this), formula bar, ruler and headings.
- Freeze panes – if you have a large amount of columns (with headings at the top) and/or rows (with headings on the left) in a particular tab then you can keep those headings on screen as you scroll right and down by freezing panes. Select the cell in just under the column headings and just to the right of the row headings and select the freeze panes option from the view menu
- Charts, Sparklines and Smart Art graphics– ‘A picture can often say more than words’ and excel some fantastic presentational tools to add a visual dimension to your spreadsheet:
- Charts – there are a wide range of charts from lines, bars and Pie charts to the more exotic donut and, bubble and radar charts. A wide range of formatting options provides the ability to create the look you are after.
- An in cell type of chart with bar, line and win/loss options. Useful for showing trends against a number of rows
- SmartArt Graphics – this has a whole range of charts including lists, hierarchy, relationship and many more. These are useful to illustrate particular concepts and can be linked to your data
- Dashboards – create summary outputs for your manager or customer. These are often presented in a four quadrant style containing several of the following components
- A heading highlighting the key message
- A text box containing the three or four key points that you wish to communicate
- Charts to illustrate trends and other key results
- Summary tables which included using the camera tool – i.e. picture of another part of the spreadsheet (where the table is created) which automatically updates as the table is updated
- Miscellaneous
- Protection – protect worksheets, workbooks and or VBA code to stop accidental overwriting of formulas or other key parts of a spreadsheet. Particularly useful if you are sharing a spreadsheet with others
- Hyperlinks – in common with many other programs Excel offers a hyperlink capability which allows the user to jump to particular points I the spreadsheet. Useful if you have many tabs or if you have many tables in a single tab
- Creating flow diagrams using shapes – the shapes tool can create flexible diagrams. I find it is particularly useful to illustrate the key data and calculation flows in a spreadsheet. Keep you diagrams as simple as possible by using a limited number of shapes with connecting lines that do not cross over each other.
Excel Monkey 