PRINTABLES & HOW TO GUIDES

Microsoft Excel

How-to guides, cheat sheets and practical help for spreadsheets and formulas.

PUBLISHED RESOURCES

Choose a resource

Open a resource to read, save or print it using the options provided.

Browse All Resources28 resourcesSort by:

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Getting Started with Excel

Excel is a spreadsheet application used to organise information, perform calculations, analyse data and create tables, charts, budgets, lists and reports. This page explains how to open Excel, create a workbook and understand the basic parts of a spreadsheet before entering or calculating data.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Entering and Editing Data

Excel cells can contain text, numbers, dates, times and formulas. Entering information consistently is important because Excel treats different types of data differently when it sorts, filters, formats and calculates.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Formatting Cells and Worksheets

Formatting changes how worksheet information is displayed without necessarily changing the underlying value stored in a cell. Consistent formatting makes data easier to read and helps distinguish headings, labels, dates, currency, percentages and calculated results.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Basic Formulas and Calculations

Formulas allow Excel to calculate results from worksheet values. A formula begins with an = sign and can use numbers, cell references, mathematical operators and functions. Using cell references means results can update automatically when the source values change.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Relative, Absolute and Mixed Cell References

When a formula is copied, Excel can change its cell references automatically. The $ symbol controls whether a row, a column or both remain fixed. Understanding this is essential when one formula needs to be copied across a larger table without referring to the wrong cells.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Essential Functions

Excel functions are built-in formulas designed to perform specific calculations. A function begins with =, followed by the function name and its arguments inside parentheses. For example, =AVERAGE(B2:B10) calculates the average of the values from B2 through B10.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

If and Ifs Logical Formulas

Logical formulas allow Excel to test a condition and return a result based on whether that condition is met. IF is used for one logical test, while IFS can test several conditions in sequence.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Sumif, Sumifs, Countif and Countifs

Conditional functions calculate only the records that meet specified criteria. SUMIF and COUNTIF use one condition, while SUMIFS and COUNTIFS can apply several conditions at the same time.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Xlookup and Vlookup

Lookup functions search for a value in one area of a worksheet and return related information from another. They are useful when working with product codes, staff IDs, customer numbers, price lists and other data where one value identifies a record.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Index and Match

INDEX and MATCH can be combined to perform flexible lookups. MATCH identifies the position of a value within a range, while INDEX returns the value from a specified position in another range. The combination is particularly useful when working with older Excel versions or spreadsheets that already use INDEX and MATCH.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Text Functions

Text functions help combine, separate and extract parts of text stored in cells. They are useful for names, reference numbers, product codes, addresses and imported data that needs to be reorganised without retyping every record.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Date and Time Formulas

Excel stores recognised dates and times as numeric values, which means they can be sorted, compared and used in calculations. Date formulas are useful for deadlines, due dates, service periods, staff records, project schedules and other information where the amount of time between dates matters.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Rounding, Percentages and Number Calculations

Excel can round calculated values, calculate percentages and apply percentage increases or decreases. It is important to distinguish between changing how many decimal places are displayed and changing the actual value returned by a formula.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Creating and Using Excel Tables

An Excel Table is a structured range of related data with built-in filtering, formatting and expansion. Tables are useful for lists such as expenses, customers, stock, staff records and sales because new records can be added while the table structure remains organised.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Sorting and Filtering Data

Sorting changes the order in which records are displayed, while filtering temporarily hides records that do not meet selected criteria. Both tools are useful for large lists, but the entire related data range must be included so that records remain together correctly.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Freeze Panes and Worksheet Navigation

Large worksheets can contain hundreds or thousands of rows and many columns. Freeze Panes keeps important headings or identifying columns visible while you scroll, while Excel's navigation tools help you move quickly through large areas of data.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Conditional Formatting

Conditional Formatting changes the appearance of cells automatically when their values meet specified rules. It can help identify overdue dates, high or low values, duplicates, targets and patterns without changing the underlying data.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Creating and Editing Charts

Charts turn worksheet values into a visual comparison or trend. A useful chart should make the data easier to understand, not simply decorate the worksheet. Choose the chart type according to what the data needs to show, then label it clearly enough for somebody else to interpret it.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Pivottables

A PivotTable summarises a larger data set without changing the original records. It can quickly total, count or compare information by categories such as department, month, product, customer or status, and the layout can be rearranged without rebuilding the source data.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Data Validation and Drop-Down Lists

Data Validation controls what can be entered into selected cells. It can restrict entries to particular numbers, dates or text lengths, or provide a drop-down list of approved choices. This helps keep data consistent and reduces errors in shared or frequently used spreadsheets.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Cleaning and Checking Data

Spreadsheet calculations, lookups, filters and reports depend on consistent source data. Cleaning data means identifying duplicates, unwanted spaces, combined fields, blanks, errors and inconsistent data types before those problems affect later calculations.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Printing and Page Setup

Worksheets are designed primarily for a screen, so a spreadsheet that looks correct in Excel may not print neatly without adjustment. Use page setup, print areas, scaling and print preview to control which cells are printed and how they are divided across A4 pages.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Protecting Worksheets, Cells and Workbooks

Excel protection can reduce accidental changes to formulas, layouts and workbook structure. Worksheet protection, workbook protection and file encryption serve different purposes, so choose the control that matches what needs protecting.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Sharing and Collaboration

Excel workbooks stored in supported cloud locations such as OneDrive or SharePoint can be shared with other people and edited collaboratively. Sharing permissions, comments and version history help teams work from one controlled workbook rather than creating multiple conflicting copies.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Formula Auditing and Troubleshooting

When a formula returns the wrong result, the problem may be in the formula itself or in one of the cells feeding into it. Excel's Formula Auditing tools help trace relationships between cells, step through calculations and identify errors without rebuilding the worksheet blindly.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Keyboard Shortcuts and Formula Cheat Sheet

This reference brings together frequently used Windows Excel shortcuts and the main formulas covered in this guide. Shortcuts reduce repeated Ribbon navigation, while the formula list provides a quick reminder of function structure and purpose.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Practical Workplace Exercises

These exercises combine the main Excel skills covered in the guide. Build each workbook or worksheet yourself, then check the formulas, formatting and results rather than treating completion of the individual commands as proof that the spreadsheet is correct.

Protected previewPurchase this resource or use an active membership to open the complete PDF.

Final Skills Check and Ribbon Reference

This final page provides a practical check of the core Excel skills covered throughout the guide, followed by a reference showing where the main commands are found. A skill should be marked as complete only when it can be carried out correctly in a workbook and the result can be checked.