Tech Tips
[Not yet joined the group? Join now]
Excel Classics for CAs – Essential Shortcuts & Time-Savers
| Shortcut / Action | What it does | Classic |
|---|---|---|
| =COUNTIF($B:$B,B2)>1 | Highlights duplicate PANs with Conditional Formatting | #01 |
| =COUNTIF($F:$F,A2)=0 | Finds records missing from another list | #01 |
| Ctrl + ` | Switch to Formula View (see formulas instead of results) | #02 |
| Trace Precedents | Formulas → Trace Precedents – shows cells feeding a formula | #02 |
| =CELL("filename") | Returns the full path and filename of the current workbook | #02 |
| Protect Sheet | Review → Protect Sheet – lock formula cells, leave inputs unlocked | #03 |
| Data → Workbook Links | Check for external links before sending a workbook | #03 |
| Name Manager | Formulas → Name Manager – clean unused or broken named ranges | #04 |
| Ctrl + End | Jump to the last used cell – reveals excess formatting | #04 |
| Avoid full-column formulas | Use actual data range or Excel Tables instead of entire columns | #05 |
| =CLEAN() / =TRIM(CLEAN()) | Removes hidden characters from imported data | #06 |
| Open and Repair | File → Open → Browse → Open ▼ → Open and Repair | #07 |
| Watch Window | Formulas → Watch Window – keep critical totals visible | #07 |
Before filing, let Excel find the data problems.
1. Duplicate PANs
Conditional Formatting → New Rule → Formula:
=COUNTIF($B:$B,B2)>1
Instantly highlights duplicate PANs.
2. Check PAN format
Use Conditional Formatting to flag PANs that don't follow the expected 10-character pattern.
Useful for large imported lists where manual checking is impractical.
3. Find records missing from another list
If the PAN/client ID is in Column A and the comparison list is in Column F:
=COUNTIF($F:$F,A2)=0
TRUE → the record is missing from Column F.
Let Excel find the exceptions. You review them.
Three ways to understand what an Excel workbook is actually doing.
1. Ctrl + `
Switch to Formula View and see formulas instead of results.
Useful for spotting hardcoded values in working papers.
2. Find the cells feeding a formula
Select a formula → Formulas → Trace Precedents
Excel shows which cells feed that calculation.
3. Find where the workbook is saved
Enter:
=CELL("filename")
Excel returns the full path and filename of the current workbook.
Useful when working with multiple client folders, shared drives or files received from different team members.
Don't just review the numbers. Know the workbook behind them.
One accidental keystroke can silently change an entire working.
Use:
Review → Protect Sheet
Keep the input cells unlocked and protect the formula cells.
Particularly useful for:
• GST / audit reconciliation sheets
• Staff working templates
• Shared computation files
• Finalised workings awaiting review
Also check for external links before sending a workbook:
Data → Workbook Links
A formula may depend on another Excel file sitting only on your computer.
Protect the formulas. Check the links.
Huge Excel file? Don't immediately blame your computer.
1. Check Named Ranges
Formulas → Name Manager
Look for:
• Unused names
• Names showing #REF!
• Ranges referring to unnecessarily large areas
Broken or unnecessary named ranges can make large workbooks slow and unpredictable.
2. Check the used range
Press:
Ctrl + End
If Excel jumps thousands of rows beyond your actual data, excess formatting may be bloating the workbook.
3. For very large linked workbooks
Formulas → Calculation Options → Manual
Do the heavy work, then switch back to Automatic before finalising.
Sometimes the problem is the workbook, not the laptop.
Two common causes of unnecessarily slow Excel workbooks:
1. Full-column formulas
A formula applied to an entire column may make Excel calculate hundreds of thousands of cells when you need only a few thousand.
Use the actual data range — or an Excel Table where appropriate.
2. Volatile functions
Functions such as OFFSET() and INDIRECT() can recalculate frequently and may slow large workbooks.
If a large working becomes sluggish, these are worth checking.
Make Excel calculate what you need — not what you don't.
Imported data often looks fine — until a formula refuses to work.
For data from AIS, broker statements, bank exports, PDFs or websites:
=CLEAN(A1)
removes hidden non-printing characters.
If spaces are also causing trouble:
=TRIM(CLEAN(A1))
Then use Paste Special → Values if you want to replace the original text.
And for GST uploads, watch the date column.
The GST portal expects dates in the required text format — Excel's automatic date conversion can create upload errors.
Clean the data before doing the reconciliation.
Three Excel problems that can cause unnecessary panic.
1. Workbook won't open?
File → Open → Browse → Open ▼ → Open and Repair
Try Repair first. If that fails, try Extract Data.
2. Accidentally overwrote a working?
Right-click the file → Properties → Previous Versions
An earlier copy may still be recoverable if Windows backup, OneDrive or System Protection is available.
3. Need to keep critical totals visible?
Formulas → Watch Window → Add Watch
Monitor key cells while working elsewhere in a large workbook.
Know the recovery tools before you need them.
