Category Archives: Excel

EXCEL: AVERAGEIFS With A Twist

AVGIFSA while back I wrote a post about the SUMIFS function. This blog post is about the AVERAGEIFS function. It’s slightly different to the regular AVERAGEIF function and I will describe the functionality behind it. In the end I will describe how you can use the AVERAGEIFS function using a reference to a data validation field. This provides a combo box list option which makes the function dynamic.

First things first
I know that everything I write about here can be done by filtering the dataset. The choice is entirely up to you. I like using the functions so I don’t have to filter my data constantly. If you want to join create the list with sample data as shown below. I have assigned named ranges to the various columns. This makes the function more readable. So I have named the first colored column “product” (which isn’t a very good name because it collides with the existing function name!). The second column is called “size” and the third column is called “color”. Finally the last column is called “price”. Of course you have the option just to select the various ranges like this “A2:A15”. I am a fan of naming ranges because I think that “Size” is easier to read than “B2:B15”. AVGIFS I

What we are trying to achieve
What we want to achieve is the average price range based on criteria we supply using the data. So in this case we are looking for three sets of answers.

  1. What’s the average price per product
  2. What’s the average price per product per size
  3. What’s the average price per product per size per color

The average price per product can be calculated by using the regular AVERAGEIF function. In the cells next to the list with data I added the following data so we have a place to get our answer:

AVGIFS VI

Now let’s have a look at the first function. It states =AVERAGEIF(product, “Jeans”, Price). In this case we just want to see the average of either the Jeans or the Shirts. Because we have only one criteria so we can use AVERAGEIF here. We need to use the IF because we have more than one variable (Jeans, shirts). The order of arguments of the function is important when working with the AVERAGEIF or AVERAGEIFS functions. So let’s see if we can spot the difference: First we have the AVERAGEIF function. This function first wants to know the range where to look for the criteria. The next criteria is to look for what in that specific range (your criteria) and finally you select the column on which Excel should calculate the average.

AVGIFS VIII
This is what the AVERAGEIFS function looks like:

AVGIFS VII

Now here’s the hard part. In contrast to the AVERAGIF function this function starts with the range that needs to be averaged (the last argument in the AVERAGEIF function), the second argument is the range we are going to use to look for the specified value and finally you provide the specific value you are looking for in the previously set range.

So if we want to know the average price for Jeans we use the following:
=AVERAGEIF(product, “Jeans”, price) which reads as follows
Look in the product range for the criteria “jeans” and look in the price column. From all the records you have found show me the average.

Now for the AVERAGEIFS function which we use to get an answer to the question:
Show me the average price for the product “Jeans” with the size “s”.

The hard part is the difference in order in comparison to the regular AVERAGEIF. The AVERAGEIFS version starts with the range that needs to be averaged and after that you set the various criteria. In our example this leads to the following AVERAGEIFS function:

=AVERAGEIFS(Price, product,”jeans”,size,”s”)

Which translates like show me the average price for the product “jeans” where the size is “s”.
By the way it seems that Excel doesn’t mind if you use capitalized letters in your criteria!

In our last sample we see that the question is as follows:

Show me average per product per size per color. So we have an additional criteria set. For the AVERAGEIFS function that makes no difference. Look at the picture and see what function has been entered to tackle this question.

Adding a bit of functionality using data validation
Using the AVERAGEIFS function this way can be tedious because I do have to change the criteria used in the function to see other values when for example the product is Shirt instead of Jeans. To tackle this problem I decided to add data validation to cells. This way I can use comboboxes to make my choices without having to change mu function. Let’s see how this is done:

AVGIFS X

and now we come to the nice part of adding this combo box to the AVERAGEIFS function.
Instead of typing the value “Jeans” or “Shirt” we just refer to the cell which contains the data validation and the choice we have made in that cell!

AVGIFS V

So look at the image above and see that the AVERAGEIFS function is not looking at “Jeans” or “Shirt” anymore but instead it is looking at a cell reference which contains our drop down combo boxes. What ever you choose will be the variable for the AVERAGEIFS function.

But wait… what if it goes wrong?
Everyone who works with Excel knows that you might get the famous #DIV/0 when something is not supplied or is not available. Most of them just leave that and will carry on. I am the person who likes to provide a little error handling just in case. So to finish the function of properly I embedded the whole AVERAGEIFS function in the IFERROR function. Now when a certain combination is not possible just because the option is not there you will see “Not Available” instead of #DIV/0! which looks better in my opinion!
AVGIFS IV

And here is what the end result might look like:

AVGIFS III

Every time you change the combo box value to a different setting the function will recalculate and show you the outcome for that specific combination. How cool is that!

 

Excel: Find Duplicates.. Do Not Remove Them!

Find_DuplicateOne of the finer tasks in Excel is working with lists. When working with lists, one of the most common questions is “are there any duplicates”. Now most people know that there is a button at their disposal the famous “Remove Duplicates”. This is great but it just removes them. How about if you want to inspect the data before it is removed? There are various ways to find duplicates in your lists. Conditional formatting offers a great way to highlight duplicate values in your list. Personally I have been using a simple function to check for duplicates. It gives me control of what I can do when it finds duplicates and how to handle them. Let me show you what I mean.

Sample data
To give you an idea how to follow the steps I use some sample data from the old Northwind database. Just a couple of rows with data which I have enhanced with some duplicate values so you get the picture.

Find_duplicates_I

Sort the data and add an additional column
The first step I take is sorting the data. My preference is to use the Data tab because it has this great group of functionality you can use at once.

Find_duplicates_IISo sort the data and we are ready for step 2. The second step I take is adding an additional column which I call “Duplicate”.

Find_duplicates_III
Now the third step I take is I add an IF statement (function) in cell A2.
Find_duplicates_IVThe IF function is pretty straight forward. I compare the value of cell B2 with the value of cell B1. If it has the same value it should show “Duplicate”and otherwise leave the cell empty. Once you have entered the function press Enter and copy it down in column A. It should look something like this:

Find_duplicates_VAs you can see it skips the first new value and every value after that which has a duplicate value gets the word Duplicate in front of it.

Duplicates on more columns
I know what you are thinking. This is one column but what if I want to check more columns? Well basically you take the same steps as above. In the example above I checked for duplicates in the first column. Now I will check the first and second column for duplicates. Have a look at the picture below:

Find_duplicates_VI

As you can see I enhanced the function a bit. Using the & -sign I tied two values together to match the other two values that are tied. So basically I say look at the value of cell C2 and stitch the value of cell D2 to it. These combined values should be equal to the values of cell C1 & D1. If this is the same value then show “Duplicate” otherwise leave empty.

Finding a whole duplicate row
Knowing what we know now we can easily look for a duplicate row. Just concatenate all the values from every column using the & -sign. Then compare that with all the concatenated values of all cells from the row above. Copy that formula down and you are good to go.

Find_duplicates_VII

Concluding
Working with lists of data is currently one of the most common tasks to do at a job. When you are like me and working with lots of data having the knowledge to de-dup is mandatory. Find the best way that fits your needs. For me it is the simple IF-function that does the trick without having them removed straight away 🙂

 

 

 

Excel: Freeze And Stop Scrolling!

Freeze_Panes_IWhen working with long lists in Microsoft Excel you will probably want to see the top row all the time so you can see the labels. If you start scrolling down the default setting in Excel is to scroll the headers (labels) as well. That way you can’t tell what the column headers are when you are scrolling down. This is where the Freeze Panes functionality steps in. Far to often I see people struggling with this option while actually it is pretty straight forward to use. In this blog post I will show you the use of Freeze Panes to keep you from losing the list headers. (click on the images for larger views)

The goal…
Here is what we are trying to accomplish. In the picture below you see part of a list which has no formatting. So we want to keep the top row so when we scroll down the header labels will stay visible.

Freeze_Panes_II

As you know by default Excel will scroll down everything so you will not see your header labels anymore.

Freeze_Panes_V

Various options
There are various options to choose from. By far the easiest one is to create a table out of your list. Click somewhere in your list. Click on the Home tab and click on the Format as Table button.

Freeze_Panes_III

This will convert your list into a table. Now try scrolling down and see what happens with your headers? Wow the column letters disappear and are replaced by your header labels. How cool and effective is that!. If you don’t want to have the annoying filter arrows just remove the filter by clicking on the Data tab and the Filter button. Right out of the box functionality for you.

Freeze_Panes_IV

I don’t want my list to be a table!
I can hear you say that. Well in that case you can use the Freeze Pane functionality. We remove the format as table functionality and try to accomplish the same with Freeze Panes. Make sure you have placed your cursor somewhere in the list. Now from the View tab click the Freeze Panes button (arrow) and choose Freeze Top Row. Excel will automatically freeze the top row for you.

Freeze_Panes_VI

Scroll down…
You can now scroll down and your header labels will stay visible as requested. You can apply the same if you want to keep your first column visible. In that case choose the option Freeze First Column. Ok we are getting there! But here is the next challenge…..

I want it both ways!
Sure you want both ways because that is what Excel users do, scroll right and down. So what we are looking for is the top row frozen and the first column frozen. How to accomplish this.

Freeze_Panes_VII

In the picture above I have selected the first row and the first column just for clarification. You don’t have to select the first column and the top row to get things done. It is the position of the active cell that is important here. To freeze the first row and the first column you have to position your cursor in the correct cel. In this case you should set the active cell to B1 (highlighted yellow in the picture below). Now Excel knows it needs to freeze the top row and the first column.

Freeze_Panes_VIII

So it is of to the View tab and the freeze panes button. Click the little arrow and click Freeze Panes. Your first column and top row are now frozen!

Now for the last challenge….
I don’t want the first column frozen but I want the first three columns to freeze and the top row as well. To clarify I have selected the first three columns and the top row again. I have highlighted the applicable active cell that should be activated and after that it is a round trip to the View tab and the Freeze Panes button where you choose the Freeze Panes option.

Freeze_Panes_IX

Rule of thumb here
So basically the rule of thumb here is as follows. You place the active cell right to the column(s) you want to freeze. So if you want the first 7 columns to freeze you place the active cell in column 8. Same applies to your top rows. If you have more rows on top that you would like to see than place the active cell in the number of rows + 1. Example: you have three top rows you would like to see and seven columns you would like to freeze. You place the active cell in column eight and row four. Now you can choose freeze panes and you are set.

Unfreeze?
Freeze_Panes_XISimple as can be. When you had enough of all the freezing just choose unfreeze from the same View tab and Freeze Panes button. You are back to square 1. So nothing to select first just go straight to the View tab and click the Freeze Panes button!

Final thoughts…
Freeze panes is great functionality in Microsoft Excel. I think this should not be so hard to use. Every now and again I get feedback from my courses where someone has seen the light. Finally I get how this works. That’s what we are aiming for right?

 

 

Show – Hide Objects in Microsoft Excel

Selection_Pane_Excel_IVI wrote a blog post about the Selection Pane in Microsoft PowerPoint a while ago. You can read it here. Now since this is such a great tool for your Office applications I think you should use this in Microsoft Excel as well. But to my surprise a lot of people don’t know about this great feature in Excel. So to get this cleared I will show you how you can use this in Excel.

Scenario: a dashboard with some objects in it.
Suppose you are creating a nice looking dashboard in your Excel sheet. You add a couple of nice boxes, pivot tables and a picture. After examining the dashboard you see a couple of things you might need to add to the sheet. In most cases the shapes get in the way. You may have to move them around a bit to get to the part of the sheet where you need to add additional info. After adding the additional information you have to move back everything into its original place. This can be a time-consuming task just to make the dashboard look more like you want it to. Here is where the Selection Pane comes into play. Let’s take a look at an example.

Dashboard:

Selection_Pane_ExcelThis dashboard has several objects to play around with. Now to reveal those go to the Home tab and at the far right you will see the button which says “Find and Select“. From this button choose the last option: Selection Pane.

Selection_Pane_Excel_IIThere you go. You are presented with a selection pane which reveals all objects on that specific sheet. Now let’s see what we can accomplish by clicking on the little ‘eye’ icons on the right of the displayed objects. In our example we see that ‘rectangle 3’ is covering our cells. If you wanted to change the field name from the small pivot table just above the second slicer you had to remove the rectangle first. By clicking on the ‘eye’ icon of the rectangle 3 object you see that this is object is temporarily hidden.

Selection_Pane_Excel_III

You can make all the necessary changes you would like without having to move the rectangle out of its original place. When done all you have to do is click once more on the ‘eye’ icon in the selection pane and the rectangle will be shown again!

So instead of moving all your objects around your sheet to get the task done give the selection pane a chance to prove itself to you. You will see that it makes your live a whole lot easier.

Excel: How To Create Diagonal Headers

DiagonalI don’t know how many lists and tables in Excel i  have seen the past few years. Some of them are real artworks and others just plain and simple. Somehow there is one type of table/list header that always makes an impression. I am talking about the lists with the diagonal headers. Aways wanted to know how people create these headers? Well follow the steps below and before you know it this one will be in your Excel tool belt as well!

Why diagonal headers in the first place?
That is the question to start of with. Sometimes your column headers are just to wide for the data the column actually hold. But you do want that good label header. Setting the header vertical is one option, but setting it diagonal just add that little touch to easier readability. So this is what we are after: (click on the image to see a larger version)

Diagonal_VIII

First step is to select the header row (only the cells that contain data please! you don’t need the entire 16000 cells in row one).

Diagonal_I
With the first header row selected right-click and choose format cells from the menu.

Diagonal_II

You will now see a familiar dialog pop up. Select the second tab ‘Alignment’ and set the orientation to 45 degrees. just drag the red dot… ( as shown in the image).

Diagonal_III

Now we set some nice color by choosing the cell style color. But wait that is not what we are after? Nope we have to adjust it a little more.

Diagonal_IV

Make sure the header is still selected. Now open the cell style menu once more. Now choose the first option under the Data and Model group. I chose calculation.

Diagonal_V

Diagonal_VII

Some adjustments…
Now you see that your color will change to the cell style you have chosen. Don’t panic you can reset the color later to the color of your liking. But he, it is starting to look the way I want it 🙂 Now for some final adjustments choose the color style you had before. The headers will stay diagonal and your color is applied.

Diagonal_VIII

Users still want to filter their data remember….
As a final touch make a table out of this list. Easy thing is just to go to the table style menu under the ‘Format as Table‘ button. Choose one of your liking and make sure the header row is also in the selection area. You now have a fully functional table with filter possibilities with diagonal headers! Adjust column widths and you are good to go. Lists anyone?

 

EXCEL: SUMIFS – DEMYSTIFIED

AutoSumDon’t you just love SUM? The Excel development team does, they even gave it a huge button in the ribbon. If you need to sum up a range of numbers you just click the AutoSum button and you are done. Working with Sum just made our lives just that more convenient. So you are ready for the next steps with SUM and are looking for a sum of values based on a condition. Enter SUMIF. Note: my regional settings require a ; (semi column) as a argument delimiter. Depending on your settings this can also be a , (comma). So replace accordingly in your functions.

Conditional Summing

Ok, let’s see how this SUMIF function works. We have a small range in Excel and would like to sum the values based on a condition in that range. Take a look at the following scenario in the image below:
SumIfs_I

We see a range with shelf, quantity, color and size. The easy part is to get the sum of quantity by just clicking the autosum button. But in this case we would like to see the sum of quantity from shelf A. So we are dealing with a condition here. That’s where SUMIF can help you.

SUMIF takes three arguments:

  • What is the range I have to look in
  • What is the condition I have to look for in the range you provided in step 1
  • What is the range I have to sum

Now look at the image again. I have outlined the ranges in the SUMIF for you. I double check by clicking the cells in the quantity column which meet my criteria (Ctrl+click). Excel shows me the result in the statusbar. This is correct! Simple right? Add this function to your toolbox now.

Conditional Summing with two or more conditions

We take our summing to the next level. We use the same scenario but add another condition. So we are stuck with two conditions. SUMIF does not allow for two conditions (at least not with some tricks or cheating). If you are using Excel 2007, 2010, 2013 or beyond…. and you probably are, you have the possibility to use the SUMIFS function. This specific function is designed to take more conditions.

NOTE: if you are using SUMIFS for the first time you will notice that the order of arguments has changed slightly. Be aware of that!

Same scenario as described earlier but this time we want to see the sum of quantity of shelf A where the color is Red. Take a look at the image below:

SumIfs_II

The most important thing to notice is the order of arguments. In this case the function wants to know the following arguments:

  • what is the range I have to sum
  • what is the first range I have to look in
  • what is the first condition I have to look for in the range you specified in the previous step
  • what is the second range I have to look in
  • what is the second condition I have to look for in the previous range you provided.
  • and so on…..

to double check I Ctrl+click every cell in the quantity column which meets my criteria and look in the statusbar. Yep the sum is the same as the output of my function! We are on a roll here… So add this to your toolbox as well.

Finally the challenge

Now for our last scenario we seem to be asking a lot of Excel because we don’t get the ouput we expect. What’s wrong… what are we doing wrong here and how to solve this.

The question we want answered is: we would like to see the sum of quantity from shelf A OR shelf C. Fairly easy question right? Excel doesn’t seem to think so. Based of what we have learned so far we start our SUMIFS function and fill in the variables as described. See image below:

SumIfs_III

The functions seem perfect but Excel gives us an ouput of 0. You check once more.. change the function a bit and decide to write it from start again. Talking to yourself out loud to hear if you are asking the correct question. Recognize this?

Here’s the issue. SUMIFS sees your function as a AND situation and that is not what we are looking for. So technically Excel is looking for a shelf AC which doesn’t exists and therefore returns 0. To be more blunt… SUMIFS is always an AND combination. It does not work with an OR condition. In this case we are looking in the same column for two conditions. Although it sounds like a AND situation we are technically looking for an OR situation.

So how do we solve this?

Many of my students like the SUMIFS function. They want to use it no matter what. They don’t like to hear ‘You can also use this function or calculate it some other way‘. SUMPRODUCT is often a function that will fly by when discussing the SUMIFS. We will use the SUMIFS for the OR situation. take a look at the image below:

SumIfs_IV

The trick we are using here is the use of an Array. It goes beyond the scope of this post to explain this completely here. But to summarize you start with a regular SUM function which is followed by a SUMIFS. The arguments for the SUMIFS are provided. So look in the shelfs range and sum the quantity. The last argument is the ‘new’ argument. You place your search conditions between curly brackets. This is where you define the OR situation. Because the SUMIFS does not ‘get’ this it will provide single values based on those two conditions. The regular SUM function which you started with will sum these single calculated values to a total sum!

Add another value. If you want to add another value to your search condition in the same column you just add it to the curly brackets and press Enter. Voila (that’s French for there you go :-)).

So your final function could look something like this:
=SUM(SUMIFS(Q16:Q33;P16:P33;{“A”;”C”;”F”})) – that is if you have a F value in your column as well of course.

Now add that one to your toolbox as well and you are good to go SUMMING.

Excel: Conditional Formatting – Color Max Value in Range

CF_IQuestion: can you tell me how to color the maximum value in a cell range with conditional formatting? Yes I can and i will show you how it is done. Simple and effective. First open Excel and create a range with random numbers not sequentially but in random order. Follow the next steps:

– Select the range of cells you would like to apply the conditional formatting on
– On the Home tab click on the button Conditional Formatting (see pic. on the left)

Take a look at the various options to choose from. That are some fine options to choose from. We might be thinking about using the =MAX function for the question. However i think that i have a better solution to offer you. Click the second option from the menu Top/Bottom Rules. That little black arrow on the right is an indicator that another menu will appear. From that menu choose the first option (as shown below…)

CF_II

See those three dots at the end of Top 10 Items… well that indicates that a dialog window will open. From that dialog change the default settings from 10 to 1 and choose your coloring. The image below shows the default setting an the setting after we have customized it.

CF_III

Now all we have to do is click OK and your formatting will be applied on the selected range. The highest value should now be colored. The great advantage of Conditional Formatting is that it is dynamic. So if a number changes and that becomes the highest number the appropriate cell will be colored and the previous one will be white again.

CF_IVNow this is one way to do the Conditional Formatting for this question. You can also choose to add a new rule yourself. In that case choose New Rule… from the menu. You will see the next dialog pop up. Choose the third option from that dialog and apply the same settings (top 1 – color) and click OK.

CF_V

That is all there is to it! Simple, effective and colorful.
Homework: how to apply conditional formatting to the minimum value in a cell range?

Happy coloring 🙂