Here are 10 common Excel mistakes. Avoiding them will save lot of rework and duplicated efforts.
Let’s start with very common Excel mistakes – Usage of “Merged cells”
- Usage of merged cells – This will break sorting & formulae if we want to select them!
- Instead use: Cntrl+1 > Alignment -> Center across selection
- Not using Table format – Would be hard to manage dynamic data
- Instead use: Cntrl+T, Creating pivots with this table also is helpful
- Not locking cells in formulae before dragging – this shall break the formulae for the further cells
- Tip: use $ in reference. Instead of E10, use $E$10. Pressing F4 will toggle the $. Try it.
- Nested IFs – Confuses the logic
- try IFS(), SWITCH() or Vlookvup()
- Typing dates manually – difficult to sort or calculate further
- use DATE() function
- VLOOKUP with hardcoded column numbers – might error out when the structure changes
- I use Xlookup() most of the time. It’s easy too, and I don’t need to remember the column number, nor I need to select the entire range of table array
- Not using IFERROR() function. Very common for amateurs – Throws out errors in multiple places if dragging the formula. Also looks ugly. Common errors – #N/A, #DIV/0!
- Wrap the entire formula under IFERROR() with a texted output or any other reference cell if errored out
- Copy pasting while in filtered mode – pastes the cells in between that are filtered out
- Go with XLOOKUP/VLOOKUP
- Not utilising the power of Power Query – hectic work of manual cleaning
- Though there are some tricks such as flashfill, I use power query. To automate, and cleanup once for all.
- Not checking the existing ones before adding a new conditional formatting rule – rules may conflict
- Review the existing rules via Manage Rules
Habituating these tips and avoiding these Excel mistakes will make life easier while cleaning the data.
Take look at other Excel posts
Click here for some Excel Freebies. These are templates that can be used for reporting/maintaining data.
Need a solid Excel foundation and want to freelance with this tool, explore tutorials from ExcelJet or Chandoo.org
#Data cleaning #Data organizing #power query #Excel Mistakes