Excel Expert Ditches Five Habits for More Reliable Spreadsheets

Tony Phillips, an experienced Microsoft Office user and document producer, has refined his Excel skills over the years. He's identified five habits that used to make his spreadsheets more challenging.

The first habit he ditched was merging cells. While it may seem convenient to merge headers or labels across rows or columns, it can cause problems with filtering, sorting, and PivotTables. Instead, Tony keeps each row as a complete record and repeats relevant values when necessary. He also turns his datasets into formatted Excel tables.

The second habit he stopped doing was leaving blank rows in his spreadsheets. Although they may look tidy, empty rows can confuse Excel and lead to issues with sorting and filtering. To avoid this, Tony keeps related records together or uses conditional formatting to visually distinguish different groups.

Tony also used to color cells by hand, but he's now relying on Excel's conditional formatting feature. This way, highlighting changes automatically as the data evolves, reducing inconsistencies and out-of-date formatting. He's also started using cell styles to maintain consistent formatting throughout his spreadsheets.

The fourth habit Tony eliminated was hard-coding values in formulas. For instance, if a tax rate is embedded directly into a formula, it can become outdated when the rate changes. Instead, he stores changeable values in their own cells and references them from his formulas. This ensures that his spreadsheets remain accurate even after external factors shift.