20 Excel Tips Only the Pros Know – Do You?
If you've been working with Excel for a while, you know it’s a powerhouse for data manipulation and analysis. But no matter how long you've been crunching numbers, there are always new tricks to learn to make your life easier.
Here are some tips and tricks we've picked up over the years that have significantly improved our workflow. Chances are, you'll find at least one or two that you did not know, so take a quick look!
Work Faster with Accelerate Excel
Simplify your Excel work with the Accelerate ribbon comprising tools for M&A Transaction/Deal Advisory, FDD and other Excel power users.
1. Sort Subtotal Rows
Sorting data is straightforward, but sorting subtotal rows can be tricky. To sort subtotals, collapse the data to show only the subtotals, then sort. This keeps your grouped data intact and organized.
How does that work?
Select the rows you want to sort.
Press Tab to pick the column to sort by.
Press the shortcut: Alt > A > SA for ascending order.
Note that you’ll need SUBTOTAL formulas instead of SUM formulas for this to work properly.
2. Select Pane / Go To Special
Excel’s Go To Special menu allows you to select only those cells meeting certain conditions such as blanks, visible cells, only those with formulas etc.
Excel’s Go To Special menu
You can open the Go To Special menu by pressing Alt > G, then Alt + S.
Interested in more selection options? Check out Accelerate Excel’s Super Select and Modify Select tools, offering you all the selection options you need.
3. Paste Special
After copying (Ctrl + C), avoid using Ctrl + V to paste as it pastes everything. Instead, use Alt + E + S to open the Paste Special menu, then choose the option you need:
Excel’s Paste Special menu
Interested in more? Check out Accelerate Excel’s Copy & Paste tool for additional options and the possibility to copy-paste multiple range selections.
4. Use Wildcards for Flexible Criteria
When working with filters or search functions, use wildcards like * for more flexible criteria. For example, *2023* finds any cell containing "2023".
In a SUMIFS function, using * as a criterion effectively bypasses that condition, acting as a placeholder for future use if needed.
5. Utilize PivotTables for Analysis
PivotTables are essential for summarizing large datasets. They allow you to quickly rearrange data to view different perspectives, making it easier to uncover insights.
Ever had to convert a PivotTable into normal cells for presentation and customization purposes? Check out Accelerate Excel’s Convert Pivot tool.
6. Line Breaks in Formulas
Line breaks in Excel formulas can be added by pressing Alt + Enter, making complex formulas more readable. It's a great way to organize and debug long formulas without losing track.
7. Edit Directly in Cells
Deactivate ‘Allow editing directly in cells’ to gain additional functionality, where double-clicking a cell will navigate you to the first reference in the cell’s formula. To edit the formula, use F2.
How to deactivate this?
Go to File > Options > Advanced > Uncheck ‘Allow editing directly in cells’.
Advanced Excel Options > Editing options > Allow editing directly in cells
8. Suppress Conversion of Account Numbers Starting with 0
Excel will convert anything that looks like a number to number formats. For example, this means that account numbers that start with 0, the 0 will be dropped. To prevent this you can add an apostrophe (‘) which will stop Excel’s automatic number conversion.
Need to add ‘ or other text to many cells at once? Check out Accelerate Excel’s Add Text tool.
9. Grouping vs. Hiding Rows and Columns
It's better to group rows and columns rather than hiding them. Hiding can lead to a lack of visibility, increasing the risk of confusion or missed information. Grouping, on the other hand, enhances transparency and keeps your data well-organized.
10. Remove Source Tabs Without Breaking Links
When you need to remove source tabs but have formulas referencing those source tabs, you can move the sheets out of the workbook and then break external links. This way the formulas referencing the source tabs will be converted to values.
11. Center Align vs. Merge Cells
When you’re tempted to merge cells, try using the Center Across Selection option instead. Merging cells can cause complications and limit flexibility, especially for those who rely on keyboard shortcuts. Center Across Selection, however, keeps the cells separate while visually centering the content, preserving full functionality.
12. Avoid Using Direct References for SUM Operations, e.g. ‘=A1+B2’
Instead, always use functions like SUM or SUBTOTAL, especially when dealing with nested hierarchies. Why? For consistency and because if a reference isn't a number, SUM or SUBTOTAL will still work, whereas direct references will throw an error.
13. Select All Sheets
You need to apply a change such as removing gridlines on all sheets? Right-click on a sheet and click on ‘Select All Sheets’.
You can remove gridlines by going to the ‘View’ ribbon and unselecting ‘Gridlines’.
14. Hard Saves – Don’t Trust Autosave
Autosave is great, but it’s not foolproof. Make a habit of manually saving your work regularly. This ensures you don’t lose data in case of a crash, accidental overwriting and other issues.
15. Use Conditional Formatting Sparsely
Most of you use conditional formatting to check for duplicates. Remove it afterwards. Conditional formatting takes up a lot of resources and makes your workbook slower.
Want to easily list and navigate all the cells with conditional formatting in your workbook? Check out Accelerate Excel’s Objects tool.
16. Avoid ‘Blindly’ Copying and Moving Sheets to Your Workbook
Copying or moving sheets that contain numerous named ranges or external links can bloat your workbook, cause performance issues, and, in the worst cases, corrupt your workbook entirely. Instead of moving the entire sheet, consider copying and pasting just the contents to avoid bringing over unwanted links or names.
17. Use SUMIFS and COUNTIFS Instead of SUMIF and COUNTIF
Even if you have only one condition, opt for SUMIFS and COUNTIFS. Sticking with the -IFS versions from the start ensures consistency and makes it easier to add more conditions later without needing to overhaul your formulas.
18. Customize the Quick Access Toolbar
Add frequently used commands to the Quick Access Toolbar for easy access. Right-click any command and select "Add to Quick Access Toolbar" to save time navigating menus.
19. Master Excel Keyboard Shortcuts
In your first few years working with Excel, you'll spend so much time in it that not learning and using shortcuts would be a missed opportunity.
Check our list of the most essential shortcuts: Essential Excel Keyboard Shortcuts for M&A Deal Advisory.
20. Learn PowerQuery and PowerPivot
PowerQuery and PowerPivot are essential tools for anyone working with large data sets in Excel. PowerQuery simplifies importing, cleaning, and transforming data from multiple sources (e.g., separate Excel files for each month or entity? No problem!).
Meanwhile, PowerPivot lets you build data models to quickly slice and dice your data, enabling faster analysis and deeper insights. Using PowerPivot also enables you to use CUBEVALUE and CUBEMEMBER formulas, which are great for dynamic and sliceable analysis like in a PivotTable but without constraints. Learn more.