Here are 10 common mistakes in Excel. Avoiding them will save lot of re-work.
- 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 will make life easier while cleaning the data.
#Data cleaning #Data organizing #power query