15 Essential Excel Settings to Optimize Your Workflow

Customizing Excel for Efficiency

• 💡 Excel's default settings are designed for average users, but this video offers 15 game-changing adjustments for more advanced users.
• 🔑 A free cheat sheet is available to help you remember these settings without needing to search online.

Handling Data Entry and Formatting

• 🚫 To prevent leading zeros from disappearing in numbers like invoice or phone numbers, you can type an apostrophe before the number or format cells as text.
• ⚙️ In newer Excel versions, disable 'Remove leading zeros' under Data options, or use custom number formats to automatically add spaces to phone numbers.
• 📊 For PivotTables, you can disable the automatic use of table structured references in formulas by unchecking 'Use table names in formula' under Formulas options.

Enhancing Visuals and User Experience

• 🖼️ Turn off distracting light gray grid lines via the View tab to make dashboards look more professional.
• ✍️ Utilize the AutoCorrect options under Proofing to create shortcuts for frequently typed phrases, reducing typos and saving time.
• 🤖 For Microsoft 365 users, hide the distracting Copilot icon by selecting 'Show Copilot icon only for highly relevant suggestions' in the Copilot options.

Streamlining PivotTable and Formula Work

• 📈 Customize the default PivotTable layout by importing a preferred layout or setting options in PivotTable options to avoid reformatting each time.
• ❌ Disable the 'Generate GETPIVOTDATA' option in PivotTable Analyze to use standard cell references instead of the cumbersome GETPIVOTDATA function.
• 🔄 Ensure backward compatibility for older Excel versions by checking workbook compatibility under File > Info > Inspect Workbook, to avoid issues when sharing files.
• 📋 Use the Clipboard feature (Home tab > Clipboard group launcher) to store and paste multiple copied items, which is useful for building complex formulas step-by-step.

Advanced Customizations and Integrations

• 📏 Adjust ruler units (inches, cm, mm) in Advanced options under the Display section for design-related tasks like labels or templates.
• 📂 Set Excel to automatically open specific files on startup by pasting their folder path into the 'Open all files in' option under Advanced settings.
• 🌐 Turn off the automatic conversion of web and network paths to hyperlinks in AutoCorrect options to prevent messy formatting.
• 🖱️ Enable Select Objects mode (Home tab > Find & Select) to easily select and move multiple overlapping objects as a single unit.
• 🌐 For Edge browser users, disable 'Open office files in the browser' in download settings to ensure Excel files open in the desktop application.
• 🔍 Use the Focus Cell feature (View tab) to highlight the active row and column of a selected cell, aiding data tracing in large spreadsheets.