Microsoft Excel — Core Features Guide for Microsoft 365 Users

Microsoft Excel — Core Features Guide for Microsoft 365 Users

This guide covers Microsoft Excel in Microsoft 365 — from building your first workbook and writing formulas, to creating charts, analysing data with PivotTables, validating data entry, and sharing spreadsheets with colleagues via OneDrive. Whether you are tracking expenses, analysing sales, or managing a project, this guide covers the tools you will use most.

Workbook & Worksheet Basics

Creating and saving a workbook

1. Open Excel → click New → Blank workbook.
2. Press Ctrl + S to save. Choose a name and location — save to OneDrive to enable AutoSave and co-authoring.
3. A workbook can contain multiple worksheets (tabs). Click + at the bottom to add a new sheet.
4. Right-click a sheet tab to rename, move, copy, or delete it.

Navigating cells

Freezing rows and columns

1. Click the cell below the row(s) and to the right of the column(s) you want to freeze.
2. Go to View → Freeze Panes → Freeze Panes.
3. The headers stay visible as you scroll through large datasets.
4. To remove: View → Freeze Panes → Unfreeze Panes.

Formulas & Functions

Writing a formula

• Every formula starts with =. Click a cell and type = followed by the expression.
• Reference other cells by their address (e.g. =A1+B1) or name.
• Use the Enter key to confirm a formula, or Escape to cancel.

Essential functions

Absolute vs relative references

• A relative reference (e.g. A1) adjusts when a formula is copied to another cell.
• An absolute reference (e.g. $A$1) stays fixed regardless of where the formula is copied. Press F4 to toggle between reference types when writing a formula.

Formatting Data

Cell formatting

1. Select cells and press Ctrl + 1 to open the Format Cells dialogue.
2. Choose from: Number (currency, percentage, date, time), Alignment (wrap text, merge), Font, Border, and Fill.

Conditional formatting

Conditional formatting automatically highlights cells based on their values — useful for spotting trends or outliers at a glance.

1. Select the cells to format.
2. Go to Home → Conditional Formatting.
3. Choose a rule type:
   • Highlight Cell Rules — colour cells greater than, less than, equal to a value, or containing specific text.
   • Data Bars / Colour Scales / Icon Sets — visually rank values within the range.
   • New Rule — write a custom formula (e.g. highlight a row if a date in column C has passed).

Sorting & Filtering Data

Sort

1. Click anywhere in the data range.
2. Go to Data → Sort.
3. Choose the column to sort by and the order (A–Z, Z–A, smallest to largest, oldest to newest).
4. Click Add Level to apply a secondary sort (e.g. sort by department, then by surname).

Filter

1. Click anywhere in the data and go to Data → Filter. Dropdown arrows appear on each column header.
2. Click a dropdown to filter by specific values, text, or number conditions.
3. Multiple filters stack — only rows matching all active filters are shown.
4. Click Clear in the Data ribbon to remove all filters.

Charts & Data Visualisation

1. Select the data you want to chart (include column headers).
2. Go to Insert → Recommended Charts to see chart types suited to your data, or choose a specific type (Bar, Column, Line, Pie, Scatter).
3. Click the chart to select it. Use the Chart Design and Format tabs to:
   • Change chart type or layout
   • Add or remove axis labels, data labels, and a legend
   • Apply a colour scheme
4. Drag the chart to reposition it on the sheet, or move it to a dedicated chart sheet: right-click → Move Chart.

PivotTables

PivotTables summarise large datasets — counting, summing, and grouping data without modifying the original.

1. Click anywhere in your data range.
2. Go to Insert → PivotTable. Choose to place it on a new or existing sheet, then click OK.
3. In the PivotTable Fields panel (right side):
   • Drag fields to Rows to group data (e.g. by department or month).
   • Drag fields to Values to calculate totals (sum, count, average).
   • Drag fields to Columns or Filters to add dimensions.
4. Right-click a value in the PivotTable → Value Field Settings to change the calculation (e.g. from sum to average).
5. Add a PivotChart: click inside the PivotTable → PivotTable Analyze → PivotChart.

Data Validation

Data validation restricts what users can enter into a cell — useful for enforcing consistent data entry in shared spreadsheets.

1. Select the cells to restrict.
2. Go to Data → Data Validation.
3. Under Allow, choose the type: Whole number, Decimal, Date, Time, Text length, or List.
4. For a dropdown list: choose List and enter the allowed values separated by commas, or select a cell range.
5. Add an Input Message (shown when the cell is selected) and an Error Alert (shown when invalid data is entered).
6. Click OK.

Sharing & Co-authoring

1. Save the workbook to OneDrive or SharePoint to enable sharing.
2. Click Share (top-right) and enter email addresses.
3. Choose Can edit or Can view and add an optional message. Click Send.
4. Multiple people can now edit the workbook simultaneously. Each person's cursor appears in a unique colour.

Tip: Use Comments (Review → New Comment) and Notes to leave context for collaborators without changing the data.

Troubleshooting

Formula returning #VALUE! or #REF! error

• #VALUE! — the formula contains incompatible data types (e.g. text where a number is expected). Check that all referenced cells contain the correct data type.
• #REF! — a cell reference is invalid, usually because a referenced row or column was deleted. Re-enter the formula referencing the correct range.
• #N/A (in VLOOKUP/XLOOKUP) — the lookup value was not found. Confirm the value exists in the lookup range and that there are no leading spaces.
• #DIV/0! — the formula is dividing by zero. Wrap with: =IFERROR(formula, "") to suppress the error display.

AutoSave is greyed out

• AutoSave only works for files saved to OneDrive or SharePoint. Save the file to your OneDrive folder to enable it.

PivotTable not refreshing after data changes

• Right-click the PivotTable → Refresh.
• To refresh automatically when the workbook opens: PivotTable Analyze → Options → Data → Refresh data when opening the file.

Conditional formatting not applying to new rows

• Edit the conditional formatting rule and expand the range to include future rows (e.g. change $A$1:$A$100 to $A$1:$A$1000).

Related Guides

• Getting Started with Microsoft 365
• Managing Users in Microsoft 365
• Microsoft 365 Security — MFA, Passwords & Account Protection

Need Help?

Need help setting this up? We can manage your Microsoft 365 for you — from setup to ongoing support.

Find out more about our Microsoft 365 managed service →