The Cathexis Excel-lent tips & tricks

Published on August 10, 2016 in Buzz, Favourite Resources, Ideas, News

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!

Peter

  1. Conditional formatting – great for catching duplicates, outliers, and errors.
  2. 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.
  3. NETWORKDAYS formula: tells you how many work days are between two dates (excludes weekends as well as any holidays you specify).

Carinaback to back

  1. Fill series: Here’s a ‘how to’ video.
  2. Visualization: Use data bars (found in conditional formatting) to create back-to-back charts.
  3. 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

Veronica

  1. 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.
  2. The COUNTIF formula; for quick counts and sums of text (not just values) in your selected cells.
  3. 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’.