Tech Tips
Word / Excel for Compliance & Data Handling
Excel practices focused on compliance use-cases, data accuracy, and avoiding common portal upload and formatting errors.
Convert Excel to Clean PDF (Proper Formatting)
What it does:
Ensures Excel reports convert into properly aligned, readable PDFs.
How to use it:
In Microsoft Excel:
Go to: Page Layout → Set Print Area
Adjust Scaling → “Fit Sheet on One Page” (if required)
Then: File → Save As → PDF
Pro tip:
Use “Page Break Preview” to check exactly how content will split across pages.
Open Corrupted Excel Files Without Panic
What it does:
Helps recover Excel files that fail to open, show errors, or crash repeatedly — often due to file corruption or improper saving.
How to use it:
In Microsoft Excel:
Go to: File → Open → Browse
Click arrow next to Open → Select “Open and Repair”
Then choose:
• Repair → Attempts full recovery
• Extract Data → Recovers only values/formulas if repair fails
Pro tip:
If the file still doesn’t open, try opening Excel in Safe Mode (hold Ctrl while opening Excel) and retry.
Instantly Spot Document Changes
Problem:
Comparing two versions manually is slow and error-prone. Important edits can slip through unnoticed.
Quick Fix:
- In Microsoft Word, go to: Review → Compare → Compare
- Select the original and revised documents.
- Instantly see all changes — insertions, deletions, edits — highlighted clearly.
Pro Tip:
- Use *Combine* to merge edits from multiple reviewers.
- Switch to *Side-by-Side View* for easier navigation.
- Save the compared version as a new file to preserve the audit trail.
Where it helps:
• Agreement revisions
• Client-edited drafts
• Policy or report changes
Why it matters:
Manual checks miss things. Word’s Compare ensures no clause, figure, or edit escapes your attention — critical for compliance and client trust.
Lock Only Formula Cells in Excel
What it does:
Prevents accidental editing of formulas while allowing data entry in other cells—useful in shared working files.
How to use it:
In Microsoft Excel:
1. Select entire sheet → Format Cells → Protection → Uncheck “Locked”
2. Select only formula cells → Check “Locked”
3. Go to: Review → Protect Sheet
Pro tip:
Use a simple password and share it only with senior staff to maintain control without blocking workflow.
Shrinking Bloated Excel Files (50MB+)
Problem:
Excel files sometimes explode in size due to hidden excess formatting — not actual data.
Quick Fix:
• Press Ctrl + End
→ If it jumps way past your last data row, the file is bloated.
- Select those extra blank rows/columns.
- Go to Home → Editing → Clear → Clear Formats.
- Save the file with a new name.
- Pro Tip:
- • Delete unused rows/columns entirely for a cleaner reset.
- Use the Inquire Add-in → Clean Excess Cell Formatting (if available).
- Save as .XLSB for even smaller, faster files.
- Where it helps:
- • Audit working papers
• GST/Income-tax computations
• Large Excel-based reports - Why it matters:
- Cleaning excess formatting can shrink files by 70–90%, speed up calculations, and prevent those dreaded “Excel Not Responding” freezes.
Select Only Visible Cells in Excel
In Microsoft Excel:
After applying filters:
Select the column →
Press Alt + ;
Excel selects only visible cells.
Now copy and paste safely.
Useful for:
• Filtered client lists
• Extracting selected rows
• GST reconciliation sheets
Prevents copying hidden rows accidentally.
Paste Values Without Carrying Formulas
In Microsoft Excel:
Copy the cells →
Press Ctrl + Alt + V → V → Enter
This pastes Values Only.
Useful for:
• Removing formulas before sharing
• Freezing computed numbers
• Cleaning imported sheets
No broken references later.
See All Formulas in a Sheet at Once
In Microsoft Excel:
Press Ctrl + `
(the key above Tab)
Excel switches to Formula View.
All formulas become visible instead of results.
Press the shortcut again to revert.
Useful for:
• Reviewing spreadsheets quickly
• Detecting hard-coded numbers
• Audit review of working papers
One shortcut reveals everything.
Instantly Select Entire Data Blocks
In Microsoft Excel:
Place cursor inside the table →
Press Ctrl + A
Excel selects the full data region.
Press Ctrl + A again → entire sheet selected.
Useful when:
• Applying formatting
• Creating tables
• Running filters
No dragging across thousands of rows.
Find Mismatches Without Scanning
In Microsoft Excel:
Suppose:
Column A = Books
Column B = Statement
In Column C, use:
=A2<>B2
Drag down.
TRUE = mismatch
FALSE = match
Now apply Conditional Formatting → Highlight TRUE.
Instant reconciliation view.
Useful for:
• GST vs Books
• Trial balance comparison
• Two client lists
• Before/after adjustments
No manual checking.
Let Excel flag the differences.
Compare Two Excel Files Properly
Steps:
1. Open both files
2. View → View Side by Side
3. Enable Synchronous Scrolling
Now both sheets scroll together.
Perfect for:
• Revised financials
• Two GST workings
• Before/after adjustments
Often faster and clearer than VLOOKUP.
Smart Excel Trick Most Professionals Miss
Create a live “screenshot” of any data — without copying formulas.
In Microsoft Excel, use the hidden Camera tool.
One-time setup:
File → Options → Quick Access Toolbar
Choose “All Commands” → Add Camera
How to use:
• Select any range — cells, summary box, Pivot, or chart
• Click Camera
• Click anywhere to place it
It inserts a live image of that selection.
Change the original numbers → the image updates automatically.
Why this is powerful for CAs:
• Create a clean P&L snapshot on Page 1 that auto-updates when detailed sheets change
• Share management summaries without exposing formulas
• Build compact KPI dashboards
• Remove gridlines and clutter while keeping live linkage
It looks like a screenshot.
It behaves like a link.
Clean reporting. Zero duplication.
Excel slowing down with large files?
Before adding formulas:
• Convert ranges to Tables (Ctrl + T)
• Avoid full-column formulas (A:A)
• Replace volatile functions (OFFSET, INDIRECT)
Result:
Faster recalculation, fewer crashes.
Excel recalculating too slowly?
Switch to Manual Calculation:
Excel → Formulas → Calculation Options → Manual
Re-enable to Automatic *before finalising*.
Common in large workbooks with linked sheets.
Excel crashing or freezing on large files?
Fix this setting:
File → Options → Advanced
✔ Disable hardware graphics acceleration
Why this helps:
• Prevents GPU driver conflicts
• Stabilises large ledgers, pivots, Power Query
• Very effective on older laptops
One setting. Big improvement.
Most GST Excel upload errors come from DATE columns.
GST portal expects:
dd-mm-yyyy in TEXT format.
Common issues:
• Excel auto-converts to US format (mm/dd/yyyy)
• Dates stored as serial numbers
• Cells left as “General”
Reliable fix:
1. Select the entire date column
2. Home → Number Format → Text
3. Re-enter ONE date (copy-paste it down)
Excel forces all dates into proper text format.
This alone resolves most “Invalid date format” GST upload errors.
