Tech Tips

Excel for Compliance & Data Handling

Excel practices focused on compliance use-cases, data accuracy, and avoiding common portal upload and formatting errors.

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.