Performance Tuning

Optimize an Excel workbook when using DVDAddin with large data — more than 10,000 rows, many UDFs, hundreds of sheets.

Measure before you optimize

Tools

Tool	Purpose
Windows Task Manager	See the RAM and CPU of EXCEL.EXE while opening a file / while recalculating
Process Explorer	sysinternals.com — more detail than Task Manager (Working Set, Private Bytes, handles)
Sysinternals Process Monitor	Track file I/O if you suspect the disk or an antivirus file scan is the bottleneck
Stopwatch + F9	The simplest way: switch to Manual calc, press F9, count the seconds
The Batch print stage report	Built-in — see below

Baseline measurement

Before optimizing, measure a baseline:

1. Close every other application.
2. Open the workbook and record the load time.
3. Trigger a recalculation (F9) and record the time. To recalculate everything, including cached cells: Ctrl + Alt + Shift + F9.
4. Save and record the time.

After optimizing, measure again and compare. Measure on the same machine, with the same file, with no other applications open — only then do the numbers mean anything.

Top 10 causes of a slow workbook

#1 — Too many cell-level UDFs

Add-in UDFs run slower than Excel's built-in functions, and Excel does not calculate UDFs across multiple threads (see Multi-thread).

Detect: The workbook has thousands of =dvd...(...) cells and recalculation takes a long time.

Fix:

• Convert the UDF results to static values (Ctrl+C → Paste Special → Values).
• Or use a ribbon command instead of the UDF. For example, instead of 1000 =dvdTranslate(...) cells, select the range and run Translate (Dịch ngôn ngữ) once — the results are written straight into the cells and no formula is left to recalculate.

#2 — Volatile functions

Excel recalculates a volatile function every time ANY cell changes, even an unrelated one.

Volatile functions in DVDAddin — exactly 5 of them:

Function	Notes
dvdAutoHide	Hides/shows rows by condition — it has to be volatile to keep up with the data
dvdSumVisible	Sums the visible cells — hiding/showing rows raises no recalculation event, so it has to be volatile
dvdMVLookup	Returns multiple results
dvdMCLookup	Returns multiple results across multiple columns
dvdUniqueV	List of unique values

Built-in volatile functions: NOW(), TODAY(), RAND(), OFFSET(), INDIRECT().

Detect: Edit one cell on another sheet and the status bar shows "Calculating (XX%)" for a long time.

Fix:

• Replace OFFSET(A1,1,0,10,1) with A2:A11 (hard-coded).
• Replace INDIRECT("Sheet1!"&A1) with CHOOSE / IF (a chain of ifs).
• Use NOW() in a single cell only, and have the other cells reference that cell.
• For the other four volatile functions: keep the cell count down to a few dozen, do not spread them over 5000 rows.

#3 — Whole-column VLOOKUP

=VLOOKUP(A1, BangGia!A:Z, 8, FALSE) — Excel scans the whole of column A on BangGia for every cell.

Fix:

• Switch to a fixed range: =VLOOKUP(A1, BangGia!$A$2:$Z$1000, 8, FALSE).
• Or use INDEX/MATCH.
• Or convert BangGia into an Excel Table (Ctrl+T) and reference it by column name.

#4 — Conditional Formatting with formulas

Every CF rule with a formula runs for every cell in its range on every recalculation.

Detect: The workbook has 50+ CF rules over large ranges.

Fix:

• Consolidate ranges — replace 100 rules on 100 separate cells with 1 rule on the combined range.
• Simplify the formula — use a cell value check instead of a complex formula.
• Disable CF temporarily: Home → Conditional Formatting → Clear Rules (for debugging only).

#5 — Complex Data Validation

A cell with the Data Validation =COUNTIF(...) = 0 recalculates every time a value changes.

Fix: Simplify the list. Use a static Named Range instead of a dynamic one.

#6 — Too many images / shapes

A workbook with many embedded images uses a lot of memory and renders slowly.

Detect: An .xlsx file larger than 50MB with little cell text.

Fix:

• Use Resize Images (Chỉnh cỡ ảnh) before embedding — lower the resolution in bulk.
• Use Pic Comment (Comment ảnh) instead of embedding directly into the cell.
• Save as .xlsb (Binary Excel) — see File size optimization.

#7 — Oversized Pivot Tables

A pivot built on a source of 100k+ rows recalculates slowly.

Fix:

• Use an Excel Table as the source (faster than a range).
• Pivot → Options → Data → "Refresh data when opening file" = OFF (refresh manually).
• Use the Data Model + Power Pivot for very large data.

#8 — Files shared over the network

A file opened from a network drive (\\server\share\file.xlsx) lags on every save.

Fix:

• Copy it locally → edit → upload it again.
• Or use OneDrive sync (the file is local, syncing happens in the background).

#9 — Too many external references

='[OtherFile.xlsx]Sheet'!A1 — Excel has to read the other file when it recalculates.

Detect: The file opens slowly and shows an "Update External References" popup.

Fix:

• Data → Edit Links → Break Link → the formulas become static values.
• Or gather the figures you need into one snapshot table inside the file itself and update it manually in batches.

#10 — Conflicts with other add-ins

PowerPivot + Solver + Analysis ToolPak + an antivirus add-in + DVDAddin — every add-in adds to Excel's startup time.

Fix: Turn off the add-ins you do not use (File → Options → Add-ins → Manage → Go → uncheck).

DVDAddin-specific tuning

Turn off Auto Calc / Auto Draw while entering a schedule

The DVD Cons tab has two switches that directly affect data-entry speed:

• Auto Calc (Tự động tính) — when it is on, the add-in recalculates the dates of the schedule table every time a cell changes.
• Auto Draw (Tự động vẽ) — when it is on, the Gantt chart is redrawn after every recalculation.

When entering a batch of several hundred tasks, turn both off, then turn them back on once you are done (or run Gantt Chart (Vẽ tiến độ) once). This is the biggest difference between smooth and stuttering entry on a large schedule table.

Switch Excel to Manual calculation

Default mode: Auto — Excel recalculates every time a cell changes.

For a workbook with many UDFs, switch to Manual:

• File → Options → Formulas → Calculation options → Manual.
• Press F9 to recalculate when you need to.

→ Editing data no longer drags thousands of recalculations along with it.

Network functions: which ones block Excel and which do not

This is a common misunderstanding — not every network function runs in the background:

Function	How it runs	Consequence
dvdTranslate	Synchronous, 15-second timeout	Excel waits for each cell in turn. 100 cells on a slow connection means Excel freezes for a very long time
dvdStock	Synchronous, 15-second timeout	Same as above
DVDFx	Asynchronous + a 1-hour on-disk cache	The cell shows #N/A for a moment then fills in by itself; Excel is not blocked
dvdAIExplain	Asynchronous	Same as above

What follows from this:

• Do not spread dvdTranslate down a whole column. Use the Translate command for large ranges and keep the UDF only in the few cells that need to update dynamically.
• DVDFx caches by the triple (from currency, to currency, date) in %LocalAppData%\DVDAddin\fx_cache.json with a TTL of 1 hour — 500 cells using the same USD→VND pair cost exactly one network call.
• =DVDFx(A1,B1) with A1 = B1 returns 1.0 immediately, with no network call.

Measuring with the add-in's own tool

If you suspect the print/export step is the slow one, do not guess — Batch print (In hàng loạt) prints a timing breakdown at the end of every run (see just below).

Batch Print performance

Batch print runs N iterations; at the end of the run the add-in shows a stage-by-stage timing report with percentages — one look tells you where the bottleneck is:

⏱  Tổng: 45.12s
   • Ghi driver cell:       1.23s (3%)
   • Calculate:             8.45s (19%)
   • AutoFilter:            0.12s (0%)
   • Resolve+Select sheet:  2.34s (5%)
   • Auto-fit merge:        4.56s (10%)
       ↳ Pass1 baseline:    1.10s (2%)
       ↳ Pass2 per-merge:   2.90s (6%)  (318 merge)
       ↳ Pass3 apply:       0.56s (1%)
   • Export PDF|In:         28.40s (63%)
   • Gộp PDF (PdfSharp):    0.50s (1%)

The three ↳ Pass... lines appear only when the auto-fit merged row height option is enabled. The Gộp PDF (merge PDF) line appears only when the merge-files box is ticked.

Reading the report:

• Calculate > 30% → the sheet has heavy formulas. Reduce UDFs, reduce volatile functions, reduce array formulas in the sheets being printed.
• Auto-fit merge > 20% → row heights are being fitted for too many ranges. The ranges to fit are declared in column C of the Mucluc sheet (rows 5–104, looked up by the sheet name in column B) — narrow them down to the ones you really need.
• Export PDF|In ~60-70% → normal. This is Excel exporting the file itself; the add-in cannot optimize it any further.
• Ghi driver cell / Resolve+Select unusually high → the workbook has too many sheets, or the sheet names in the Sheet-print cell are pointing all over the place.

One more thing: during a long run, do not type into any cell while it is running — Excel is busy and your input will collide with the add-in.

Memory consumption

Detect

Process Explorer (sysinternals):

• Add the "Working Set" and "Private Bytes" columns.
• Monitor EXCEL.EXE over time.

If memory keeps climbing and does not drop after the workbook is closed, suspect a COM object that some macro or add-in is not releasing.

Narrowing it down

Turn off every add-in → open the file → measure memory → turn the add-ins back on one at a time → find the one that inflates memory.

If you narrow it down to DVDAddin, send the file (or a description of the steps) through the support channel in the About (Tác giả) dialog.

Multi-thread

Excel built-in threading

File → Options → Advanced → Formulas → "Enable multi-threaded calculation":

• Default: follows the number of CPU cores.
• You can fix the thread count if you want to leave CPU for other work.

Important: only Excel's built-in functions are calculated in parallel. Add-in UDFs (every dvd... function) run single-threaded — adding CPU cores does not make them faster. This is why "reduce the number of UDF cells" is more effective than any hardware tweak.

The macro queue

A few functions have to wait for Excel to be idle before they can do their part of the job. A function that is calculating is not allowed to modify the sheet, so the following functions queue their work and run it right after the calculation pass ends:

• dvdAutoHide — hides/shows rows.
• dvdPic — inserts a picture into a cell.
• dvdMVLookup, dvdMCLookup, dvdUniqueV — write the second and later results down into outputRange (the path for Excel 2016/2019, which has no dynamic arrays).

The practical consequence: do not leave a cell half-typed and expect dvdPic to finish drawing the picture — the queue only runs once you complete the entry (Enter / Esc). For the same reason, the three outputRange-writing functions above are not suited to mass use: each cell is one more sheet write queued behind the calculation pass.

File size optimization

Save as .xlsb

.xlsb (Excel Binary Workbook) = the same features as .xlsx but stored in binary form:

• Noticeably smaller file size for workbooks with many formulas.
• Faster to open and save.
• Occasionally has compatibility problems with third-party tools that read Excel files.

→ Consider .xlsb for large files.

Clean unused cells

A workbook with an empty cell at row 1,000,000 (from a stray paste) is still treated by Excel as a big sheet.

Fix:

1. Ctrl + End → jumps to the cell Excel thinks is the last one.
2. If it jumps well past the real content, there are empty cells that still carry formatting.
3. Select the surplus rows/columns → right-click → Delete.
4. Save → close → reopen → Ctrl + End → now it jumps to the right place.

Two commands clean this up quickly: Delete unused formats (Xoá format không dùng) and Strip junk styles (Xoá Cell Style ngoại lai) — junk styles that arrive with files other people send you, which often generate thousands of surplus styles.

Compress images

File → Compress Pictures → 96 ppi (email) or 150 ppi (web).

→ The file shrinks a great deal if the workbook has many images. For images you have not inserted yet, use Resize Images to process them in bulk first.

Related

• Best Practices — general tips.
• Backup & Restore — strategy.
• Power User Tips — advanced.
• Troubleshooting — item #18: the workbook is slow after installing DVDAddin.