0

So one of the questions that comes up when I am helping customers with this is how do you calculate what percentage of your staff are compliant.

In this scenario, I have set up 16 employees who each have to do a Health and Safety and Induction course. Some of them have done it and some have not. So what percentage of my staff have their courses completed?

Here is the file I start with.

Here is the video that shows you how to do it.

 

You can view the completed file here..

 

As always, comments and suggestions welcome.

 

Continue Reading

How to compare two lists in Excel

Published on March 1, 2018 by in Excel | General

2

Recently I got asked by a colleague to help her identify compare two lists of providers in Excel. In one list there was a list of providers available in May. In the other she had a list of providers available in January. She wanted to identify what providers were in one list but not in the other. So there was a bit of a lost/found issue

lost-2747289__480

 

 

 

 

 

In order to do that, I set up a file using the following

  1. Vlookup function combined with the iferror function.
  2. Conditional formatting to highlight the missing ones
  3. Finally, filtering to just show the ones that were missing.

Step 1- Make it easy to see both sheets

Open the file which you can download here:

Vlookup_comparison_blank_97

and set up the two sheets so you can see them side by side.

  1. View | New Window (click on this once). Note that you will now see 2 after the file name.
  2. View | Arrange All. Choose Tiled and make sure you tick the box that says “Window of Active workbook” (otherwise all the files you have opened will appear on the screen.
  3. Click on each sheet so that you see May 2015 on one side and January 2015 on the other.

 

Step 2 – Use Vlookup to identify what is missing

Create a vlookup to compare the entries using the Provider_Code. Where there is a match, we will see the provider code, where there is no match we will see the infamous N/A.

  1. Click in cell B2 in the May 2015 sheet
  2. Click on Formulas | Lookup and Reference | Vlookup
  3. Click in the Lookup Value box, then click on cell A2. Note that what appears in the box is [@[PROVIDER_CODE]]
  4. Click in Table Array. Then highlight columns A and B in the January 2015 sheet.
  5. In the Col_index_num box enter 1 (this is for column 1)
  6. In the Range Lookup box, enter False.
  7. Click OK.
  8. The formula will copy down. Note that for most of the entries you see a provider code. However for some of them there is N/A – that means they are a missing entry.

Step 3 – Using Iferror() to highlight the missing entries

  1. Click in the May 2015 sheet, click in cell B2
  2. In the formula bar, click between “=” and the V
  3. Type in iferr
  4. Iferror appears in blue underneath the function. Double click on it. =iferror( will now appear to the left of the Vlookup function)
  5. Navigate to the end of the vlookup function
  6. Enter a comma (,) .
  7. Type in “No Match” (include the quotation marks)
  8. Type in )
  9. Press Enter
  10. Note the formula copies down and you will now see No Match instead of N/A

Step 4 – To apply conditional formatting

  1. Click in the May 2015 sheet, Highlight column B
  2. Click on Home | Conditional Formatting | Highlight Cells Rules | Text that contains…
  3. Type No Match into the box.
  4. Click OK. Note that all the No Match entries appear in red.
  5. Click on the filter icon beside the heading at the top of column C and filter for No Match. If you are not sure how to filter, check out this tutorial here.

 

found

Homework

If you do the same thing in the January sheet, you should end up with 7 No Matches.

You can view a copy of the completed file here.

Continue Reading

10 tips to help you work faster in Excel

Published on October 11, 2017 by in Excel | General

1

    1. Table it! If you have large data sets, convert them to a table. This means that if you scroll down to the bottom, the headings will remain visible at the top
    2. Hide! :Hide the columns/rows you don’t need.
    3. Top row visible: Freeze the first column of your data so that it’s always visible as you scroll across. Use View | Freeze Panes | Freeze Panes for this. Note that your freeze will happen above and to the left of the point you had clicked on before you activated Freeze Panes.
    4. Navigate...Learn the common navigation keyboard shortcuts:
      1. Ctrl and home to always return to A1 – no matter how far you have wandered in the spreadsheet. Think of it as Dorothy’s red slippers
      2. Ctrl and right arrow always brings you to the right hand side of the data set
      3. Ctrl and left arrow always brings you to the left hand side of the data set
      4. Ctrl and arrow down brings you to the bottom of the data set
      5. Ctrl and arrow up brings you to the top of the data set
      6. Ctrl and * allows you to highlight the data set
    5. Instant charting: You can create an instant chart by highlighting the data and pressing F11 (this creates it on a separate sheet). If you want the chart to be on the current sheet, press Alt and F1
    6. Quick Copy down of formula: When you convert your data to a table, and then enter a formula, it is automatically copied down – invaluable if you have a data set with thousands of records.
    7. Learn how to (Format) Paint: Use the Format Painter option to copy your conditional formatting from one area of your workbook to another. You can also use it if you come across formatting that you have to use but do not know how to create it.
    8. Learn the shortcuts for what you use most often: You can use the keyboard shortcuts Ctrl and + to insert rows or columns (highlight the area you want to insert them in first)
    9. Learn how to fit on a page: Make sure you know how to set the printing options so that you can get it to fit on a page rather than it printing across multiple pages and then frantically trying to stick it together. In particular, learn how to use Print Titles so that it makes your report much easier to read. You can check out a tutorial here on printing
    10. Free goodies!: Have a look at the templates available to you under File | New. Very often much of the work has already been done here and they are all free. So they are well worth checking out.

Continue Reading