Excel Frequently Asked Questions
Get answers to common Excel questions, from basics to advanced features
Getting Started
What is the difference between Excel and Google Sheets?
Excel is a desktop-based spreadsheet application by Microsoft with powerful features for complex data analysis. Google Sheets is a cloud-based alternative that excels at real-time collaboration. Excel handles larger datasets and offers more advanced functions, while Google Sheets is free and accessible from any device.
Which version of Excel should I use?
For most users, Excel 365 (subscription-based) is recommended as it includes the latest features like dynamic arrays, XLOOKUP, and regular updates. Excel 2021 is a good one-time purchase option if you prefer not to subscribe. Avoid versions older than Excel 2016 as they lack modern features.
How do I enable macros in Excel?
Go to File → Options → Trust Center → Trust Center Settings → Macro Settings. Select "Enable all macros" (not recommended for security) or "Disable all macros with notification" (recommended). For specific files, you can also use the yellow security warning bar that appears when opening files with macros.
Formulas & Functions
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is the modern replacement for VLOOKUP, available in Excel 365 and 2021. XLOOKUP can search in any direction (not just left-to-right), returns exact matches by default, and handles errors better. VLOOKUP is still widely used but has limitations like only searching to the right and requiring column index numbers.
Why does my formula show #REF! error?
The #REF! error occurs when a formula refers to cells that no longer exist. Common causes: deleting rows/columns that formulas reference, copying formulas incorrectly, or linking to closed workbooks that have been moved. To fix: check your formula references and restore deleted data or update the references.
How do I use IF statements with multiple conditions?
Use nested IF statements or the IFS function (Excel 365/2019+). For AND conditions: =IF(AND(A1>10, B1<20), "Yes", "No"). For OR conditions: =IF(OR(A1="Red", A1="Blue"), "Primary", "Other"). The IFS function is cleaner for multiple conditions: =IFS(A1>90,"A", A1>80,"B", A1>70,"C", TRUE,"F").
What are dynamic arrays and how do they work?
Dynamic arrays (Excel 365) allow formulas to return multiple values that "spill" into adjacent cells automatically. Functions like FILTER, SORT, UNIQUE, and SEQUENCE create dynamic arrays. Example: =UNIQUE(A1:A100) returns all unique values without needing array formulas or dragging.
Data Management
How do I remove duplicates in Excel?
Use Data → Remove Duplicates for a one-time removal. For dynamic duplicate removal, use the UNIQUE function (Excel 365). To highlight duplicates without removing: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. For more control, use COUNTIF formulas to identify duplicates.
What is the maximum number of rows and columns in Excel?
Excel supports 1,048,576 rows and 16,384 columns (Column XFD) per worksheet. This equals over 17 billion cells. However, performance may degrade with very large datasets. For datasets exceeding these limits, consider using Power Query, databases, or specialized data analysis tools.
How do I create a dropdown list in Excel?
Select cells → Data → Data Validation → Allow: List. Enter values directly (separated by commas) or reference a range. For dynamic dropdowns, use Excel Tables or named ranges. In Excel 365, you can use the UNIQUE function to create dynamic source lists that update automatically.
Formatting & Visualization
How do I create conditional formatting rules?
Select your data → Home → Conditional Formatting. Choose from preset rules or create custom formulas. For formula-based rules: New Rule → Use a formula → Enter formula like =$A1>100. You can apply multiple rules, manage rule priority, and use relative/absolute references for complex formatting.
What is the best chart type for my data?
Line charts: trends over time. Column/Bar charts: comparing categories. Pie charts: parts of a whole (avoid if >5-7 categories). Scatter plots: relationships between variables. Combination charts: multiple data types. Heat maps: patterns in large datasets. Choose based on your story, not aesthetics.
How do I lock cells to prevent editing?
First, select cells to remain editable → Format Cells → Protection → Uncheck "Locked". Then Review → Protect Sheet → Set password and options. By default, all cells are locked when protection is enabled. You can allow specific actions like formatting or sorting while protecting formulas.
Performance & Troubleshooting
Why is my Excel file so slow?
Common causes: volatile functions (INDIRECT, OFFSET, TODAY), excessive formatting, large arrays, external links, or complex calculations. Solutions: remove unnecessary formatting, convert formulas to values where possible, disable automatic calculation (Formulas → Calculation Options), and use Excel Tables instead of entire column references.
How do I recover an unsaved Excel file?
File → Info → Manage Workbook → Recover Unsaved Workbooks. Excel auto-saves every 10 minutes by default. For files you were working on: check File → Open → Recent → Recover Unsaved Workbooks. Enable AutoRecover in File → Options → Save for better protection.
Why can't I open XLSX files?
XLSX is the modern Excel format (2007+). If you can't open: 1) Update Excel to a newer version, 2) Install the free Microsoft Office Compatibility Pack, 3) Use Excel Online (free), 4) Try alternative programs like LibreOffice Calc. Older Excel versions can only open XLS files.
Collaboration & Sharing
How do I share an Excel file for collaboration?
For real-time collaboration: Save to OneDrive/SharePoint → Share button → Enter email addresses. For traditional sharing: Review → Share Workbook (legacy feature). Excel 365 offers the best collaboration with co-authoring, comments, and version history. Always communicate about who's editing what section.
How do I track changes in Excel?
Review → Track Changes (legacy feature, removed in newer versions). Modern approach: Use comments, or save versions with descriptive names. Excel 365 automatically tracks version history in OneDrive. For detailed tracking, consider using the Inquire add-in to compare workbooks.
Can multiple people edit an Excel file simultaneously?
Yes, with Excel 365 and files saved to OneDrive/SharePoint. You'll see real-time cursors and changes. Desktop Excel requires the file to be saved online first. Some features like macros and certain formatting options may be limited during co-authoring. Excel Online supports simultaneous editing for free.
Can't Find Your Answer?
Explore our comprehensive tutorials or use our function finder tool