13 Essential Excel Fixes for Common Annoyances

Efficient Data Handling and Visualization

• 🎯 Copying visible cells only is achieved by selecting the range and using Alt + Semicolon before copying, preventing unexpected hidden data.
• 🖼️ Create a live image preview of data on another sheet by copying the source data, pasting it as a linked picture, or using the Camera tool.
• 🔍 To get a list of unique values, use the Advanced Filter with the 'copy to another location' and 'unique records only' options, or use the UNIQUE function for a dynamic list.
• 📊 Build in-cell bar charts using the REPT function with a pipe symbol and a font like 'Playbill', which automatically adjusts with score changes.

Formula Debugging and Data Management

• ⌨️ Quickly insert today's date using the shortcut Ctrl + Semicolon.
• 💡 Debug formulas by selecting elements in the formula bar and using F9 to evaluate them (use Esc to revert if multiple elements are selected).
• 📈 The Watch Window allows you to monitor key numbers across multiple sheets in real-time, updating automatically as changes are made.
• ↔️ Rearrange data by selecting a row or column, hovering over its edge until a four-sided arrow appears, then holding Shift and dragging to the desired location.

Advanced Formatting and Customization

• 📅 Use the TODAY() function for a dynamic date that automatically updates to the current date.
• 🙈 Make data disappear without deleting by changing the cell format to three semicolons (Custom > Type: ;;;), which hides the content while preserving values in the formula bar.
• 💯 Fix percentage formatting issues by copying the number '100', then using Paste Special > Divide on the incorrect percentage values, followed by applying the percentage format.
• 📝 Create custom lists in Excel (File > Options > Advanced > Edit Custom Lists) to enable autofill for frequently used items like product names or departments.
• 🖨️ Ensure headers repeat on every page during printing by going to Page Layout > Print Titles and selecting the rows to repeat at the top.