I think it is safe to assume that Microsoft Excel is a part of every organization, in one way or another. Everything from accounting to even process documentation can be captured within its workbook functionality. For being one of the most utilized programs today, it is surprising how much people do not use it to its full potential. So much functionality goes unutilized and I feel it is worth mentioning what people are missing. Here are some features or formulas that I suspect most people are unaware of:

1. Grouping Data
   This feature allows the user to group rows or columns in such a way that they can easily be hidden or shown with the push of a button. Once grouped, a plus or minus symbol is displayed which allows for this functionality. It is an extremely useful trick, as it allows the user to compare data that is not right next to each other without having to move any data.
   • How:
     1. Create a blank row/column above/left to data (this will be for the header)
     2. Highlight rows or columns (only data, not blank row/column
     3. Group (Data Tab)
     4. Click advanced options (arrow in lower right hand corner of group section of data tab)
        1. deselect options: below/right of detail
     5. Type label title in blank row/column (format)
     6. Ungroup: Highlight Rows/Columns > Ungroup (Data Tab)

2. Freeze Panes
   A feature that allows the user to either freeze column(s) or row(s). This is extremely useful on long Excel sheets because it allows the user to bring the headers with them as they peruse through the data.
   • How:
     1. Select row below (or column to the right) of last row to be frozen
        1. For freezing both row(s) and column(s) select the cell that is directly below and to the right of the row(s) and column(s) you want to freeze
     2. Freeze Panes (drop down on View Tab) > Freeze Panes

3. Conditional Formatting
   This functionality applies user defined formatting to a set of cells automatically based on its contents.
   • Some Example Options/Rules:
     • Data bars (shows value within the cell, the higher the number – the longer the bar)
     • Highlight Duplicate/different, less than, greater than, equal to
     • Format based on percentages or average
   • How:
     1. Select Cell(s)
     2. Conditional Formatting (Home Tab)
     3. Select Options/Rules

4. Week number
   Excel automatically assigns a week number to all 52 weeks in a year (from 1 – 53). This can be extremely useful when trying to compare dates between each other. Rather than trying to compare the dates, the week numbers can be used instead.
   • How:
     1. =weeknum(cell)
     2. Output: 1 – 53, treat as an ordered list
     3. Keep in mind, if comparing more than a year’s worth of data – extend the week number beyond 53.

5. Index/Match
   When it comes to finding data, most people have heard of V or H lookups. These formulas are useful, but there is a huge drawback to using them. The issue with these formulas is that desired values to the left (vlookup) or above (hlookup) lookup cell will provide #N/A. Nevertheless, there is an alternative formula that can be used to avoid this error. Recently Mikkel Sciegienny from Spreedsheeto.com sent us a walkthrough on how to get the most out of this formula. I took a look at it and thought it was very intuitive and was worth sharing. Follow the link below to learn how this could help save you from a lot of time searching through your large Excel spreadsheets!https://spreadsheeto.com/index-match/