May 23rd, 2015 | Categories: Formulas | Tags: , ,

So a common request is switching text, so instead of “The day is Monday” we would switch “Monday” => “Tuesday” so it would ready “The day is Tuesday”. There are two functions that come up, SUBSTITUTE and REPLACE. We’ll talk now only about SUBSTITUTE, because that’s the more relevant function for this task.

So let’s start with a few lines of text:


What I want to do is replace the word “Excel” with “Microsoft Excel”. So let’s check out the SUBSTITUTE function:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Text – the original string we’ll work with (“Tomer is great at Excel”)

Old text – the word we want to replace (“Excel”)

New text – the word we want to be replaced with (“Microsoft Excel”)

So let’s write our function in B2 as follows:

=SUBSTITUTE(A2, “Excel”, “Microsoft Excel”)

Screen Shot 2015-05-23 at 10.55.44 AM


As we see it worked, it now says “Tomer is great at Microsoft Excel”, yay!

Let’s duplicate the function to the second row as well, so it will now say:

AutomateMicrosoft Excel is the largest Microsoft Excel site in the world”. That’s not right. We can see the function replaced ALL occurrences of the word “Excel” to the new word. In the first instance “Excel” was part of “AutomateExcel”, because it’s a name. That shouldn’t of been replaced.

To solve this we have the 4th function parameter: instance_num which allows us to define which instance to replace, instead of all of them.

Screen Shot 2015-05-23 at 10.56.07 AM

Let’s change our function to the follows:

=SUBSTITUTE(A3, “Excel”, “Microsoft Excel”, 2)

There we go! Now it’s perfect.

Screen Shot 2015-05-23 at 10.56.23 AM


May 19th, 2015 | Categories: Formulas | Tags: ,

When calculating dates and work periods, Excel has a lot to offer. In this tutorial we’ll want to figure out how many days each contacter we’re using will actually work, excluding weekends. We’ll use the NETWORKDAYS function

Let’s start with some data, with start and ends dates, like the following:

Screen Shot 2015-05-19 at 12.17.26 PM

Now let’s start out by doing it the old way, just subtract the two dates, to get the net amount of days between the dates. I’ve added 1 to include the dates as well.

Screen Shot 2015-05-19 at 12.17.36 PM


This doesn’t help us to much as we want to actually pay the contractor according to his work days, we’ll use the NETWORKDAYS function which works as follows:

NETWORKDAYS(start_date, end_date, holidays)

We’ll start by only using the first two parameters:

Screen Shot 2015-05-19 at 12.17.43 PM

There we have it, we calculated the number of work days (Monday-Friday for USA) those two dates fall under.

May 18th, 2015 | Categories: Formulas | Tags: ,

So in our last tutorial we talked about cell counting using COUNTIF This allowed us to count using a condition or criteria. But what if you wanted to count using a few different criteria, let’s say count all the students who are in classroom “A” and need a T-shirt size of “Small”. That requires us to use a different, but very similar function called COUNTIFS (note the S at the end)

We’ll use a similar table of data for our example, it has all the student names, their t-shirt sizes and what classroom they’re in:

Screen Shot 2015-05-16 at 11.04.15 AM

We want to calculate how many students ordered which size for each classroom (2 levels). Let’s create a separate tab to calcualte the numbers, here is the format of the second tab, no calculations yet.

Screen Shot 2015-05-16 at 11.04.27 AM


OK so let’s get to work, we’ll select the third column on the first row and start using typing “=COUNTIFS(”

Screen Shot 2015-05-16 at 11.06.15 AM


Now similar to the previous COUNTIF tutorial, we’ll start by selecting our first range and criteria. The range is WHICH cells need to be evaluated and criteria is what they need to equal in order to count them. We’ll start by taking care of the t-shirt size, so we’ll for our range we’ll select the B:B column and for the criteria we’ll select the “S” value, which is B2:

Screen Shot 2015-05-16 at 11.07.16 AM


So far we should have this shown (note this is currently showing ALL the students the marked “S” in all classrooms)

Screen Shot 2015-05-16 at 11.17.43 AM

Now let’s add another criteria to the function, to only select the students that are in classroom A. This works in the same format with criteria / value, so we’ll add our second range and second criteria.

Screen Shot 2015-05-16 at 11.19.20 AM


We’ll mark column C for the range (which is the classroom column) and the value “A” for classroom number. Here is the final value shown:

Screen Shot 2015-05-16 at 11.19.38 AM

Let’s drag down the formula all the way down:

Screen Shot 2015-05-16 at 11.26.06 AM

Are we done? Not yet. For now all the values are checking to see if the classroom is marked as “A”. We’ll change the formula on rows 6-9 to check for value “B” and rows 10-13 to value “C”.

Screen Shot 2015-05-16 at 11.28.36 AM

All done

May 16th, 2015 | Categories: Formulas | Tags: ,

Counting in Excel is one of the most basic utilities you’ll need to use in your work. At this point I’m sure you already know the basic COUNT function and how it works. The basic function counts the number of cells with a value. But many times you’ll need to count cells using more advanced logic.

Let’s take this example of data, it’s a list of students with T-Shirt sizes of each:

Screen Shot 2015-05-16 at 10.42.18 AM

A very common task would be to calculate how many students need which T-Shirt size so we can order from our supplier. So we’ll start by creating a new tab and creating a list of just the T-Shirt sizes:

Screen Shot 2015-05-16 at 10.44.34 AM

Now, we’ll need to count each size, so let’s add another column to the right called “# Of students” which will contain the final result.

Screen Shot 2015-05-16 at 10.44.48 AM

Now let’s get to business, we’ll use the COUNTIF function for this, which basically is the same as COUNT, but adds a single criteria to go along with it (example: count all the rows from A1:A100 that have the value of “S”)

So the format is:

COUNTIF(range, criteria)

Range – The range that is tested against criterion – Here will mark the cells we will be testing, in our case column A, aka “Size”

Criteria – The pattern or test to apply to range – This is the condidtion that must be true in order to count, in our case, must be equal to “S”.

So let’s get to work, we’ll select the second column in the first row to start off with:

Screen Shot 2015-05-16 at 10.44.48 AM

And define it’s value as a function using the “=COUNTIF(…)” format:

Screen Shot 2015-05-16 at 10.45.05 AM

For the range we’ll select our data, which happens to be in tab #1. We can select both columns.

Screen Shot 2015-05-16 at 10.45.18 AM


Now for the criteria parameter, we need to select what it needs to equal, which is “S”. To make this easier, we’ll just tell it to use the value in the first column, which already says “S”:

Screen Shot 2015-05-16 at 10.45.31 AM

Let’s close it up and see what the value is:

Screen Shot 2015-05-16 at 10.45.52 AM

Perfect! Let’s duplicate the logic by dragging the column down:

Screen Shot 2015-05-16 at 10.46.01 AM

All done!

November 24th, 2010 | Categories: Data Manipulation | Tags: , ,

In these two tutorials (two parts) you will learn a few cool tips regarding pivot tables:

1) To fill the empty values with zeros:Right click the base data field (circled below)

Select Table Options…
Type 0 in the empty cells, show (circled below)

Click OK.

The pivot table now should have replaced the blanks with zeros.

2) To hide the Totals column:

Right click the base data field (“Sum of Actual FTE” shown above)
Select Table Options…
Uncheck Grand Total for Rows (circled below)

The totals column should now have been removed.

3) To add a hidden filter to the pivot table:Drag the field that contains the filter into the hidden data filter area (ie – Org unit level 04)

Click the down arrow to select the org unit filter. Select the required filter (ie – Sport And Recreation)

The pivot table now shows only those positions in Org unit level 04, Sport And Recreation.

4) To show only selected position titles:

Click the down arrow to select the Position Title filter

To make selection easier drag the list outwards so more positions are visible

If there are many values in the list and you only want a few, start by un-checking Show All (shown below) to deselect all values

Select the positions you wish to show in the table. (ie – Athletes and Managers)
Click OK

The pivot table should now only contain Athletes and Managers.

5) To show percentages of position type for each position:

Right click the base data field (in this case “Sum of Actual FTE”)
Select Field Settings…

Click Options > >
Select % of row for Show data as
Click OK.

Note: In this list there are also options for % of column and % of total with the latter being very useful.

The pivot table now shows percentages but there may be error values for the rows that have no positions (as you can’t divide by zero!).

6) To remove errors from the pivot table:Right click the base data field (in this case “Sum of Actual FTE”)
Select Table Options…

Enter 0 in the For error values, show box
Click OK.

All errors should now be replaced by zeros.

November 21st, 2010 | Categories: Data Manipulation | Tags: ,

A Pivot table is in essence a way to extract data and present it in a readable form. Pivot tables are a great way to interrogate data and create tables of information that can be refreshed when the raw data changes!

Working example: you require a table that shows the following:

  • How many positions (Actual FTE) you have in total
  • Broken down by Position Title
  • Broken down by Employment Status (Perm/Temp/Casual)

Once you have the raw data you create the pivot table on your raw data:

  1. From the toolbar select Data > Pivot Table and Pivot Chart Report…
  2. Click Next
  3. Check that the data you want to include in the pivot table is highlighted in the Range
  4. Click Next
  5. If you want your pivot table to appear in a new worksheet (tab) click finish. Otherwise, If you wish it to appear in the same worksheet (ie – below the data) click Existing worksheet and select the first cell after the data and then click finish

    The pivot table skeleton should appear.

    If the Pivot Table Field List (shown on the right, above) is not in view then you will need to open it:

  6. Right click the toolbar (anywhere in the square below):
  7. Select PivotTable.
  8. Click Show Field List
  9. The fields should now be in view and you can drag the box to the right to lock it in place.

    The next step is to drag the fields you need into the table areas to create the table. The pivot table structure is as follows:

    Typical usage of the areas:
    HIDDEN FILTER AREA: Recurrently funded positions filter
    COLUMN SEPARATOR AREA: Position Type, Employment Type, Employment Basis, Classification
    ROW SEPARATOR AREA: Org Units, Locations, Position Titles
    BASE DATA AREA: Headcounts, FTE’s

  11. Drag the following fields into the areas:HIDDEN FILTER AREA: None
    ROW SEPARATOR AREA: Position Title
  12. The pivot table should look like this:

    Notice that the base data has defaulted to “Count of Actual FTE”. This means that each position has been counted as 1 irrelevant of the FTE value. If the base field was Headcount this would be correct (as all values would be 1) however it’s FTE values and they require a sum not a count.

  13. To change the base field to a sum right click the field (circled below):

    Select Field Settings…

    Select Sum.

    Click Number…
    Select Number and 2 decimal places then click OK.

    The data should have changed to decimals.

  14. To re-order the Position Type columns:Right click Casual > Order > Move to End

    You now have your finished table with the columns in order of Permanent, Temporary, Casual.
November 21st, 2010 | Categories: Data Manipulation | Tags: ,

The advanced filter can be a very useful tool when trying to remove duplicate values from a field.

Working example: To remove duplicate position numbers from an establishment report.

  1. From the toolbar select Data > Filter > Advanced Filter…

  2. Click the selector tool for the List Range (circled below)
  3. Click the header bar for the G column (Position No) to select all the data in that column
  4. It should be automatic but check the Criteria Range is the range for all the data you wish to include in the filter
  5. Tick the box “Unique records only”
  6. Click OK.The duplicates (rows 9 and 10) that contain the same position number as row 8 have now been hidden from view.

November 21st, 2010 | Categories: Data Manipulation | Tags: , , , ,

There are quick ways to use these everyday functions:

  1. Click the cell you wish to display the new value (ie – total)
  2. Select the sum button from the toolbar
  3. Check the range is correct (for the values you wish to include) If the range is incorrect you can highlight a new range
  4. Click OK. The result will appear in the cell you selected (you have to manually type in the words “Total”, “Average” etc to the left of the formula cells)
November 13th, 2010 | Categories: Data Manipulation | Tags: , ,

The AutoFilter tool is very useful for manipulating the data to find data subsets at the click of a button.

To turn the AutoFilter on:

  1. Highlight the data including the header column (if applicable).
  2. Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data on the worksheet).

  3. From the toolbar select Data > Filter > AutoFilter…(the downward arrows will appear for each heading)

Working example: You want to find out what Service Centres have a boiling point of higher than 80.

  1. Select the Boiling Point downward arrow
  2. Select Custom
  3. Select is greater than and enter 80 into the value box
    Click OK
  4. The data now only displays those rows that match the criteria. How this works is that the rows that do not match the criteria are hidden not removed so be careful when editing the data and dragging formulas as this is prone to overwrite data that is not in view without you knowing! It is advisable to copy the data from here to another worksheet and then edit it from there especially when you’re dealing with large number of rows.
  5. You can now sort in order of Boiling Point as follows:

To show all the data again:

  1. From the toolbar select Data > Filter > Show All
November 13th, 2010 | Categories: Data Manipulation | Tags: ,
  1. Highlight the data
  2. From the toolbar select Data > Sort…
  3. Select your sort and order (up to 3 sorts can be applied simultaneously).
  4. Click OK. The data should now be sorted alphabetically by Service Centre.