Intermediate Excel 2013 How-To Guide

Intermediate Excel 2013 – How-To Guide

Intermediate Excel 2013

How-To Guide

This guide accompanies the Intermediate Excel 2013training session. It covers the main points of the training session in a step-by-step “How To” manner. You can use this guide as an aid when completing workbook tasks during the session, or as a reference source after you have completed the training.

All resources used in the training session, including this guide, are available on the IT Training website:

A Note on Conventions:

Menus and menu options appear in bold italics.
E.g. InsertImage means choose the Insert menu then the Image option.
Excel features appear in bold.
E.g. “Use PivotTables to aggregate and summarise data”.
Hints, tips or asides appear within a box. E.g.

TIP: Hints, tips and asides will appear in boxes like this!

Excelhas a comprehensive Help system that can be accessed via the help icon:. The articles are also available online, so, when relevant, this guide will link to help articles to provide more information. These links will appear in an orange box. E.g.

/ More information:

Contents

Filtering Data

Clearing a Filter

Relative vs Absolute Cell References

Relative Cell References

Absolute Cell References

Excel Tables

Creating a New Table

Working with a Table

Table Styles

Sort and Filter

Inserting a New Row or Column

Calculated Column

Total Row

Structured References

Functions

Comparison Operators

Conditional Formatting

Applying Quick Conditional Formatting

Applying Rule Based Conditional Formatting

Clearing Conditional Formatting

Managing Conditional Formatting Rules

PivotTables

Inserting a PivotTable

Adding Data to your PivotTable

Pivoting a PivotTable

Changing the Field Order

Formatting a Field

Renaming a Field

Sorting PivotTable Data

Grouping a Field

Summarise a Field by a Different Calculation

Show Values in Alternative Ways

Adding Calculated Fields

Filtering a PivotTable using Slicers and Timelines

Timeline

PivotTable Design Options

Filtering Data

Data can be filtered to only display data that meets certain criteria. Filters can be applied to one or more columns. Filters can either be simple “tick box” filters or more advanced, criteria based filters.

Click within the range of data you wish to filter.
Choose Filter from the Data tab on the ribbon:

Drop-down arrows should now appear in the first row of the spreadsheet. Click on a drop-drown arrow to filter the data on that column:

To filter the data either:

a)Tick (or un-tick) the filter options. The filter will display only ticked items.

b)Use the Text/Number/Date Filters (the exact filter available will depend on the format of the data in the column) to specify the data to be displayed:

If the filter requires an input you will now be prompted for one. For example the Greater Than filter requires a number whereby only data that is greater than that number will be displayed.

/ More information:

Clearing a Filter

To remove a filter from a column:

Click on the drop-down arrow for that column.
Choose Clear Filter From…

TIP: To clear all filters choose Clear from the Data tab on the ribbon.

Relative vs Absolute Cell References

Relative Cell References

By default, when a formula is copied and pasted to a new cell (or when AutoFill is used) it will update to reflect its new location, this is known as a relative cell reference. See the graphic below for an example:

You can also use the clipboard tools: Copy and Pasteto copy a formula to other cells.

A formula that is Cut and Pastewill not update when it is moved.

Absolute Cell References

Absolute cell references “lock” a reference to a particular cell, column or row. Absolute cell references do not change when the formula is copied or when Auto Fill is used.

A cell reference can be made absolute by preceding the column letter or row number (or both) with a dollar sign ($).

Examples:

=$A$1 / The formula will always reference cell A1, no matter where it is copied.
=$A1 / The formula will always reference column A, so the row will change if the formula is copied upwards or downwards.
=A$1 / The formula will always reference row 1, so the column will change if the formula is copied to the left or right.

TIP: Use the F4 key to quickly cycle between the absolute cell reference options.

/ More information:

Excel Tables

An Excel Table can be created from a range of data. Creating a table allows data to be managed and analysed more efficiently and includes benefits such as:

Data is automatically formatted into a table format.
Filter and sorts easily available.
Formulae and functions are automatically applied to an entire column (calculated column).
A Total Row can be added to quickly summate data (and apply other functions).
Structured cell references are created to make it easier to refer to sets of data.

Creating a New Table

Click within the range of data that you wish to convert to a table.
Excel will automatically select the range of data. Confirm this is the range you wish to use. If your data contains column headers (recommended), ensure that box is ticked.

Working with a Table

Table Styles

Once a table has been created a table style will automatically be applied:

To change the style of the table use the Table Tools  Designtab on the ribbon and select a new style:

Sort and Filter

Sort and filter options are available for each column by selecting the column’s drop-down arrow on the top row of the table.

Inserting a New Row or Column

Type in an adjacent row or column and the table will automatically expand to incorporate that row or column.

Calculated Column

Enter a formula in one cell of a column and the formula will be applied to the entire column.

Total Row

A total row can be added to the table to automatically summate columns of the table. Select the Total Row tick box on the Table Tools  Design tab to add in the total row:

Other functions such as AVERAGE and COUNT are also available in the total row; use the drop down arrow on the total row to select one:

Structured References

When data is converted to a table Excel automatically creates structured references to make it easier to refer data within the table.

A structured reference begins with a square bracket and its name is based upon the column header. For example instead of referring to the data in column C as C1:C10 a structured reference is created: [Budgeted Cost]. The structured reference can then be used in formulae and functions.

NB: if you are referring to a structured reference from outside of a table, you must first specify the table name then the column in square brackets.

Example:

A structured reference used in a COUNTIF function to count the number of test scores in column “e-test 1” of the table “Table1” that are greater than 40.

=COUNTIF(Table1[e-test 1],”>40”)

For more information on structured references, refer to the Excel Help or follow the link below.

/ More information on Tables:
Structured References:

Functions

This training session covers a range of conditional (If, CountIF, SumIF, VLookup) functions. Explanations of these functions can be found in the PowerPoint slides.

The best way to find out how to use a function is to use the function wizard and the associated help file.

Click the Insert function (fx)button on the formula bar:

Search for the function:

A description of the function appears below. For detailed help on that function click the hyperlink beneath that.

Comparison Operators

Many conditional functions (such as IF and SUMIF) use comparison operators to compare two values. The table below lists the comparison operators available in Excel.

Comparison operator / Meaning / Example
= / Equal to / A1=B1
Greater than / A1>B1
Less than / A1<B1
>= / Greater than or equal to / A1>=B1
<= / Less than or equal to / A1<=B1
Not equal to / A1>B1
/ More information on functions:
Comparison operators:

Conditional Formatting

Conditional formatting can be applied to automatically format cells in a range of ways, allowing you to visualise data, spot trends and patterns, and highlight unusual values.

Applying Quick Conditional Formatting

Select the data you wish to apply the conditional formatting to:

Select a conditional formatting option: Home tab  Conditional Formatting then choose a style from DataBars, ColorScales or Icon Sets:

TIP: Quick conditional formatting is best applied to numerical data.

Applying Rule Based Conditional Formatting

Select the data you wish to apply the conditional formatting to.
Select a conditional formatting option: Home tab  Conditional Formatting Highlight Cells Rules or Top/Bottom Rules then choose an option from the submenu.

You will then be prompted for the rule settings and the formatting you wish to apply:

The dialogue will vary slightly based on the conditional formatting you have chosen, but the same principle applies: select a value in the left box (can also be a cell reference) and a formatting option in the right box (further formatting options available under Custom Format… option).

Clearing Conditional Formatting

TIP: If you wish to remove conditional formatting from specific cells (rather than the whole sheet), highlight them first.

Home tab Conditional Formatting  Clear Rules.
Choose the area you wish to remove conditional formatting from:

Managing Conditional Formatting Rules

Home tab Conditional Formatting  Manage Rules…
The Conditional Formatting Rules Manager lists active rules. You can use the manager to create new rules, edit existing rules or delete them.

/ More Information:

PivotTables

A PivotTable is an interactive table that allows raw data to be quickly summarised and analysed. You can experiment with the presentation of data in a PivotTable by moving data fields from rows to columns and vice-versa. This is where the “pivot” aspect of the name PivotTable originates.

Inserting a PivotTable

Click within your data set. The data can either be in aExcel Table format or a normal data set, however you should ensure that there are no blank rows or columns.
Select Insert  PivotTable from the ribbon:

The Create PivotTable dialogue should now appear:

Excel should automatically select your data, although you can modify this if you wish.

Choose where you want your PivotTable to be inserted, on a new worksheet or on the current worksheet.

TIP: Excel 2013 incorporates a Recommended PivotTables feature that can often be a good starting point to begin your analysis. The button is located alongside the PivotTable button on the Insert tab.

Adding Data to your PivotTable

The PivotTable Fieldslist appears in a task pane on the right of the screen when a PivotTable is selected. Ticking a field in the list adds it to the PivotTable report.

Excel adds the field to one of four areasof the report. Excel determines what it thinks is the best area based on the data in that field.

Pivoting a PivotTable

To experiment with the layout of the data in a PivotTable, drag a field from one area to another:

Changing the Field Order

A further method for altering the data layout is to adjust the order of each field in an area; this can be achieved by dragging and dropping a field above or below another field in that area. E.g. using the example above you may drag the Expense field above the Budget code field.

Formatting a Field

Numbers in a PivotTable can be formatted to better represent the data. For example cost figures may be formatted as a currency or in the accounting format.

Right click on a number in a PivotTable:

Choose Number Format…
Select a number format from the dialogue:

The number formatting should now be applied to the entire field.

Renaming a Field

Click within the field of the PivotTable that you wish to change.
PivotTable Tools AnalyseField Settings

Choose a Custom Name for that field.

Sorting PivotTable Data

To sort a PivotTable report:

Click within the field in the PivotTable that you wish to sort by.
Data  Sort
Choose the sort order; the options available will depend on the data in the field. For example, numerical values can be sorted Largest to Smallest or visa-versa.

TIP: Selecting a subtotal allows you to sort by subtotals instead of individual items.

Grouping a Field

Items in the Row Labels or Column Labels areas of a PivotTable report can be grouped to summarise the data and make it more readable.

Numbers can be grouped into different intervals. For example you may wish to group marks into specific categories (e.g. 0-10, 10-20 etc.) and add a count of those marks to ∑Values area of the report to create a frequency distribution table for marks.

Perhaps the most common way of grouping data is to group dates together. Dates (and times) can be grouped on a variety of intervals, for example, day, month, quarter or year.

To group a field:

Click within the field of the PivotTable that you wish to group:

PivotTable Tools AnalyzeGroup Selection.

Choose the start and end options for grouping (the default values are usually acceptable) then choose how your groups will be setup. Use the Ctrl key to select multiple options (e.g. Months and Years):

/ More Information:

Summarise a Field by a Different Calculation

Fields that contain numbers will usually be summarised by the SUM function when added to a PivotTable report. However, you can change how the field is summarised. For example your field may contain data relating to marks, summing this data is pretty meaningless, but changing the calculation to average will result in useful information.

Click within the field of the PivotTable that you wish to change:

Open the Field Settings for that field (PivotTable Tools AnalyseField Settings) and choose a function from the list:

Show Values in Alternative Ways

A PivotTable allows you to depict your data values in alternative ways, in relation to other rows or columns. For example you may wish to show the budgeted cost of an expense as a percentage of the total budget.

Click within the field of the PivotTable that you wish to display in an alternative manner.
Open the Field Settings for that field (PivotTable Tools AnalyseField Settings)
Click the Show Values As tab then choose a calculation method from the list

/ Calculations Explained:

Adding Calculated Fields

Calculated fields can be added to a PivotTable to extract extra information from the source data. They work by using the contents of other fields to perform calculations.

For example you may wish to calculate a Budget Deficit field: the difference between the Actual Cost and Budgeted Cost:

PivotTable Tools Analyse Fields, Items & Set  Calculated Field.

Choose a name for your field then enter your formula. Use the Insert Field button to use one of the existing fields in your formula:

/ More Information:

Filtering a PivotTable using Slicers and Timelines

A PivotTable can be filtered by moving fields into the Report Filter area. However it can be difficult to determine exactly how the report is filtered if filtering is applied to multiple fields.

A Slicer is a new feature of Excel 2010 and attempts to resolve the problem by presenting a visual representation of the filter.

To add a slicer to your PivotTable:

PivotTable Tools AnalyseInsert Slicer
Choose the field(s) you wish to filter on:

Click the data items you wish to be displayed (these appear in blue). Use the Ctrl key to select multiple items:

Timeline

A Timeline is similar to a Slicer, except it is especially designed to filter dates fields

It can be inserted in the same manner as a Slicer (PivotTable Tools  Analyse  Insert Timeline)

TIP: In Excel 2013 Slicers and Timelines can be applied to regular Tables, as well as PivotTables

PivotTable Design Options

The design of the PivotTable is controlled through the PivotTable Tools  Design tab of the ribbon. The layout group of the tab allows you to control how subtotals and grand totals are displayed and how the report is laid out.

The PivotTable Style Options and PivotTable Styles groups control the visual design of the PivotTable: