If you find yourself immersed in spreadsheets daily, you're likely aware that while Excel is a powerful tool, it can also be challenging at times. However, it doesn't have to be that way.
By utilising a few shortcuts and lesser-known features, you can greatly enhance your efficiency, minimise errors, and concentrate on the critical aspects of your work.
We've compiled a list of 20 essential Excel tips, specifically tailored for accountants and bookkeepers. These aren't just optional enhancements—they are indispensable tools that save time and reduce errors on a daily basis.
Practical Excel Tips You'll Actually Use
1. Highlight Active Row and Column with Focus Cell
Stay on track with this feature that highlights the row and column of the active cell, preventing loss of focus in expansive spreadsheets.
How: View tab → Show → Tick “Focus Cell”
2. ALT + = for Quick AutoSum
Need a swift total?
How: Press ALT + = and let Excel automatically select the nearest range and insert a sum formula.
3. CTRL + [Click] to Trace Formula Inputs
Need to audit a formula?
How: Hold CTRL and click the formula cell to instantly highlight its referenced cells.
4. Paste Values Only
Avoid copying formulas when all you need are raw values.
Shortcut: CTRL + ALT + V, then press V and Enter.
5. Toggle Filters Instantly
Easily add or remove filters across your dataset.
Shortcut: CTRL + Shift + L
6. Turn a Range into a Table
Transform ranges into tables for easier sorting, filtering, and referencing.
Shortcut: CTRL + T
7. Flash Fill Repetitive Data
Automate repetitive tasks like splitting names, reformatting ABNs, or cleaning phone numbers.
Shortcut: CTRL + E
8. Repeat Last Action
Need to repeat a recent format or insertion?
How: Simply press F4 to quickly perform the last action again.
9. Use Named Ranges for Clean Formulas
Replace cell ranges like A2:A100 with descriptive names like TotalSales for clarity and reusability in formulas.
10. Double-Click the Fill Handle
Instead of manually dragging formulas down, double-click the small square in the bottom-right of a cell to auto-fill down.
11. Format Inside a Formula with TEXT()
Need to format dates or currency within a formula output? Use the TEXT() function.
Example: =TEXT(A1, "dd/mm/yyyy")
12. Apply Conditional Formatting to Spot Errors
Highlight overdue dates, duplicate values, or other discrepancies with colours or icons.
How: Home tab → Conditional Formatting
13. Insert Today’s Date Instantly
Quickly add today’s date without typing it out.
Shortcut: CTRL + ;
14. Show All Formulas
Reveal all formulas in a worksheet for auditing purposes.
How: Press CTRL + ` (the backtick) to display all formulas.
15. Group Rows or Columns for Collapsible Sections
Organise data by grouping revenue streams, departments, or reporting periods.
Shortcut: ALT + Shift + Right Arrow
16. Use Power Query to Clean Data
Effortlessly merge, reshape, and clean datasets, especially useful for external reports.
How: Data tab → Get & Transform Data
17. XLOOKUP Over VLOOKUP
Utilise XLOOKUP for easier, more flexible data retrieval that doesn't depend on column order.
Example: =XLOOKUP(A2, B2:B100, C2:C100)
18. Use Data Validation for Dropdowns
Ensure data consistency and prevent typos with dropdown menus.
How: Data tab → Data Validation → Choose List
19. Track Key Cells with Watch Window
Monitor crucial totals or KPIs across multiple sheets without excessive scrolling.
How: Formulas tab → Watch Window
20. Use IFERROR to Clean Up Outputs
Eliminate unsightly #N/A or #DIV/0! messages in client reports.
Example: =IFERROR(A1/B1, "")
Why This Matters
Most bookkeepers and accountants are familiar with Excel basics. However, mastering these advanced tips can transform good spreadsheets into great ones. More importantly, they save precious time. Your time is far too valuable to waste on fixing broken formulas or manually filling hundreds of rows.
Whether you're cleaning a client's spreadsheet or preparing a report, these tips will enhance your speed, consistency, and confidence in your data's accuracy.
The Full Excel Tips Table
Excel Tip Command / Shortcut
• Highlight the Active Row and Column with Focus Cell: View tab → Show → Tick “Focus Cell”
• ALT + = to AutoSum Quickly: Press ALT + = in a cell below or beside a range
• CTRL + [Click] to See Formula Inputs: Hold CTRL and click a cell with a formula
• Paste Values Only: CTRL + ALT + V, then V, then Enter
• Toggle Filters On/Off: CTRL + Shift + L
• Create a Table: CTRL + T
• Use Flash Fill: Type an example, then CTRL + E
• Repeat Last Action: Press F4
• Use Named Ranges: Formulas tab → Define Name
• Double-Click Fill Handle: Double-click bottom-right corner of a selected cell
• Format Output in Formulas: Use =TEXT(value, format)
• Apply Conditional Formatting: Home → Conditional Formatting → New Rule
• Insert Today’s Date: CTRL + ;
• Show All Formulas: CTRL + ` (backtick)
• Group Rows/Columns: ALT + Shift + Right Arrow
• Use Power Query: Data tab → Get & Transform Data
• Use XLOOKUP: Use =XLOOKUP(lookup, lookup_array, return_array)
• Create Dropdown Lists: Data tab → Data Validation → List
• Use the Watch Window: Formulas tab → Watch Window → Add Watch
• Wrap Formulas with IFERROR: Use =IFERROR(formula, "")
Want More?
The Firm offers practical tools and insights for accountants and bookkeepers—no fluff, just practical advice to help you excel in your work. Subscribe for updates or follow us on LinkedIn for more time-saving tips.
Have a tip of your own? Share it with us, and it might be featured in our next post.
Ready to Level Up Even Further?
If you're looking to move beyond Excel and enhance your accounting firm's performance, explore AccountKit.
Designed for accountants tired of spreadsheets only doing half the job, AccountKit offers features like powerful client group structures, inter-entity loans, automated equipment finance registers, and correspondence tracking, all designed to streamline your workflow and make it more scalable.
Integrating seamlessly with Xero, it's a transformative tool for firms aiming to deliver greater value with less administrative burden.
Better systems.
Happier clients.
Less spreadsheet stress.
Visit AccountKit and start your free trial today.
