Ten practical habits for Excel and Google Sheets, from tables and named ranges to XLOOKUP, data validation and keyboard shortcuts.
Most spreadsheet time is lost to small, repeated jobs. These ten habits work in both Excel and Google Sheets unless noted, and each one saves time every week.
- Keep raw data in one tidy block. One header row, one record per row, no blank rows or merged cells. Every formula, filter and chart gets easier.
- Turn data into a table. In Excel press Ctrl+T. Formulas then fill down automatically and ranges grow as you add rows. Google Sheets has a similar "Convert to table" option.
- Use XLOOKUP instead of VLOOKUP. It can look left, defaults to an exact match and does not break when you insert columns. Both Excel 365 and Google Sheets support it.
- Name important cells. A cell called
VAT_Rateis much easier to read in a formula than a bare cell address. - Lock references properly. Press F4 in Excel while editing a reference to cycle through absolute, mixed and relative versions of it.
- Add data validation. A drop-down list stops typos like "Londno" from breaking your totals.
- Wrap lookups in IFERROR, carefully.
=IFERROR(XLOOKUP(...),"Not found")is tidy, but do not use it to hide errors you have not understood. - Use conditional formatting for exceptions. Highlight overdue dates or negative balances so problems stand out.
- Learn five shortcuts. Ctrl+arrow to jump, Ctrl+Shift+arrow to select, Ctrl+D to fill down, Ctrl+; for today's date, Ctrl+Z to undo.
- Leave notes for future you. A short "How this works" tab, or comments on tricky formulas, saves a lot of head-scratching six months later.
Pick two habits this week. Once they are automatic, add two more.
Five more habits for when you're ready
- Freeze the header row. In both apps, use the View menu to freeze the top row so headings stay visible as you scroll through long lists.
- Use filters instead of deleting rows. In Excel, Ctrl + Shift + L toggles filters. In Google Sheets, use Data > Create a filter. Filter views in Sheets let several people filter without affecting each other.
- Summarise with pivot tables. Instead of writing dozens of SUMIFS formulas, a pivot table can total sales by month and region in seconds. Insert one from the Insert menu in either app.
- Keep inputs separate from formulas. Put assumptions such as rates, targets and dates in clearly labelled cells, ideally on their own tab, and refer to them. Never type a number directly into a formula you'll copy.
- Show formulas to check your work. Ctrl + ` (the key above Tab on most UK keyboards) toggles between values and formulas in both Excel and Google Sheets, making it easy to spot a broken pattern.
Protect your work
- Version history: Google Sheets saves versions automatically (File > Version history). Excel files saved to OneDrive or SharePoint have AutoSave and version history too.
- Protect ranges: lock formula cells or whole sheets so colleagues can enter data without overwriting calculations.
- Use dates properly: enter dates in a consistent format, and check your locale is set to the UK, so 03/04 means 3 April, not 4 March.
Common time-wasters to avoid
- Merged cells in data tables, which break sorting and filtering. Use "Centre across selection" in Excel instead.
- Colour-coding as the only way of storing information. Formulas and filters can't easily read colours; add a status column.
- Copying and pasting the same data into several files. Link to one source or use a single shared sheet.
Sources and further reading
- Microsoft Support: XLOOKUP function
- Microsoft Support: Excel keyboard shortcuts
- Google Docs Editors Help: XLOOKUP
- Microsoft Support: Create a PivotTable
- Google Docs Editors Help: Version history
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.