Since we use all kinds of programs daily in our evaluation work, we thought we would share some of our favourite Excel tips & tricks that we find save us time!
- Conditional formatting – great for catching duplicates, outliers, and errors.
- If you highlight a group of cells, at the bottom right of the window it will give you the Average, Count, and Sum of the selected cells. Useful for quickly counting.
- NETWORKDAYS formula: tells you how many work days are between two dates (excludes weekends as well as any holidays you specify).
- Fill series: Here’s a ‘how to’ video.
- Visualization: Use data bars (found in conditional formatting) to create back-to-back charts.
- Quickly move your column to a row format (or vice versa)! Here’s how: select the area > Copy > Click on an empty cell >Home> Paste Special > Transpose
- When you have created a graph you really like, but don’t want to re-do every single future graph from scratch, simply select your fancy new chart and press ‘Copy’ then select your next autogenerated Excel chart, press ‘Paste special’ > All formats and voila! A huge time-saver.
- The COUNTIF formula; for quick counts and sums of text (not just values) in your selected cells.
- Finding duplicates: Select your data of interest> Data (tab on top row) > Filter > Advanced > ‘Copy to another location’ > (select a cell on a new page) > Check the box ‘Unique records only’.