pay-2446670_1280

Extracting the Largest and Smallest Values in a Dataset

Retrieving the maximum and minimum values is done relatively easily in Excel using the MIN() and MAX() functions, but what if you wanted to extract the five largest or smallest values?

Two lesser-known functions can help you achieve this: SMALL and LARGE. Both functions work the same way and have the same arguments: (array, k)

In the first argument (array), you select the data you want to pull your value from, and the second argument (k) is how large or small it is with respect to the data (e.g. a value of 5 would give you the fifth largest or smallest value, depending on whether you are using the LARGE or SMALL function).

You could recreate the MAX and MIN results by placing a value of 1 in the second argument, for example:

=SMALL(A:A,1) would return the smallest (minimum) value in column A
=LARGE(A:A,1) would return the largest (maximum) value in column A

However, the real value in using the SMALL and LARGE functions is being able to pull the next smallest, or largest number.

You can use the ROW() function to help you do this.

If you needed to pull the 10 largest or smallest values from a range of data, rather than manually changing the k values manually, what you can do is use the ROW() function, and use ROW(A1) in the first argument and then drag the formula down.

The relative references will change the value to A2, A3, A4, etc. Now whether you use A or B or C as the column doesn’t matter since the formula is only going to calculate the row number.

The formulas might look something like this, assuming you data is in column A:

=SMALL(A:A,ROW(A1))
=SMALL(A:A,ROW(A2))
=SMALL(A:A,ROW(A3))

The value for ROW(A1) is 1, and for ROW(A2) it is 2, so you can see how just by dragging this formula down you can easily increment your formula and pull the next largest or smallest number.

The above example would get you the three smallest values in the data set, whereas the following would return you the three largest:

=LARGE(A:A,ROW(A1))
=LARGE(A:A,ROW(A2))
=LARGE(A:A,ROW(A3))

Creating a Dynamic Dashboard in Excel





Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data


If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.

1. Ensure your data is free of error cells, as this will result in errors.
2. Have proper headings setup so that you know what you are referencing in your dashboard. 
3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table


First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables). 

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.


Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product. 

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.

For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:


Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group


Then select the how you want the dates to be grouped



Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings



Then click on Number Format and then select Accounting.



Creating a Pivot Chart


Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart



What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.

You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at. 


The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.

However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons. 



Now that the pivot chart is ready to go, you can now go about and format it how you like. 

Formatting and Tidying Up


Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat


For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers


Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.


On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.


By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.


Using Slicers in Pivot Tables

I am going to use the same pivot table that I left off with in my last post and now I am going to add slicers to show you how they can make your life easier.

***Please note slicers are a new feature in Excel 2010 and you will not be able to use them on older versions***

To insert a slicer is very simple, while having selected a cell on your pivot table, go to the Insert tab and click on Slicer.

Then you will be prompted to select which field you want to use a slicer for:

I am going to select the first four fields: Date, Customer, Store, and Sales Rep. Once you’ve made your selections, click OK.  Now you have four different slicers that are on top of your pivot table:

This is of course not ideal, so the first thing you will want to do is move them. You can easily re-size the slicers as you see the current one I have selected for Sales Rep shows circles along the edges that I can use to re-size it.

First what I am going to do is insert a column into column A so that my pivot table pushes over into the next column. I will also insert a few rows above it to make room as well.

I am doing this so it is easier to move over a slicer to the left of the pivot table.

For slicers that have long filters (for example customer names), I prefer to put those above the pivot table, otherwise the names might get cut off. For shorter names, such as the three letter month abbreviations, those can go into the column to the left of the pivot table. Because they are short, they won’t need a big column to accommodate them. However, this is just my preference and you can put slicers wherever you think is most convenient.

I’ve re-arranged my slicers so the months are to the left of the pivot table and everything else is above it:

The slicer to the left of my pivot table looks fine, but the problem is the slicers at the top only show a couple items each, and would require me to scroll to find my selection. You may need to scroll, but there is a lot of empty space that can be used otherwise.

What I want to do is add another column in the slicer. If I select the Customer slicer, under the Options tab under the Slicer Tools section, on the right-hand side there is a field for columns. The default is set to 1, but if I change it to 2 my slicer now looks like this:

This is already a big improvement since now instead of seeing just two items I see four. I can add more columns but in this case without expanding my slicer horizontally it will truncate the names. You can stretch out the slicer as you see fit and adjust the columns accordingly. After adjusted my slicers all to be two columns, my pivot table and slicers now look like this;

If you are finding it hard to line up your slicers so they are even, select the slicer and under Slicer Tools and Options, click the Align button and check off Snap to Grid and Snap to Shape. Doing this will make it easier for your slicer to lock on to other slicers and make it easier to line them up. Do this for each slicer you are having issues lining up. Or you could select all your slicers (using ctrl) and apply the settings to all of them at once.

Now that the slicers are setup they are ready to use. Slicers effectively are filters in a pivot table, but the key difference is their ease of use for any user.

Whatever options I want to filter by I can just click on in the individual slicers. In the below example I am going to select all the sales to Customer ABC and Store 1 for Rep 01. My selections below reflect these selections and now my data table has filtered this data out:

If I want to remove any filters then all I can just click on the filter icon and the red x in the top right corner of the filter. This will reset the selections for that slicer.

Based on my selections I cannot choose any option besides Oct for the Date slicer. This is because based on the criteria I have selected in the other slicers there are no other months that are available. The unavailable selections are indicated by the faded light blue selections.

Pivot Table Basics – How to Create and Setup

Pivot tables in Excel allow you to easily summarize data and group information by category. The benefits of pivot tables is you can also double click on any number to see the individual items that make up the totals. The downside is pivot tables are not always very flexible to work with.

In this introductory post I will go over the following:

  • How to create a pivot table
  • How to setup the pivot table’s rows and columns
  • Grouping dates into months, years, and quarters
  • Filtering data in a pivot table
  • Changing the formatting of fields
  • Changing pivot table values to averages
  • Show values as a % of a column or row
  • Adding more fields and changing views to tabular
  • Viewing contents that make up a cell

If you want to follow along with my example you can download my sample file here

How to Create a Pivot Table


This is a simple step, the only requirement is your columns should have headers as you can see from my sample data table below. Select any cell on your data table and click on the Insert tab and click on the Pivot Table button.


That will pop up the next screen:

Excel automatically determined my table range because I had selected a cell on my data. If I hadn’t, then Excel would not be able to pull this information. As long as you make sure you click the insert pivot table button when you have your data selected then usually Excel will get the range correct. Of course if the information is incorrect you can change the range at this screen.

You can select whether you want the pivot table in a new worksheet or the existing one. In most cases you’ll want a new worksheet. In this screen I usually just click OK as the defaults usually work fine.

Setting Up the Pivot Table


When the pivot table is generated you will see the following:

This pivot table is blank and not terribly useful right now. On the right-hand side you will see this:

This is where you will select where you want your data to show. Suppose I want to see a summary of total sales by customer and by month.

First I will find the Total Dollars field from the above list and drag it into the Values section of the pivot table.

Next, I will move the Customer field into the Rows section. Whether I put it into rows or columns will not matter, but normally you want the field with the most amount of items to go into the rows section, because if you have a lot of data in the columns section you will have to do a lot of scrolling to see all of the table. In this example I don’t have that many customers so it does not matter.

Lastly, I will move the Date field into the Columns section.

Your pivot table should look something like this:

If the dates don’t quite look like this then that is fine. In this version of Excel it automatically grouped my dates.

Grouping Dates by Month, Year, Quarter, etc…

If you want to group your dates or want to change the grouping, select one of the dates on your pivot table and under the PivotTable Tools  section, under the Options (or Analyze, depending on which version of Excel you have) tab, click on Group Field button.

After clicking that button you will see the following options:

Excel has automatically divided my data into months, quarters, and year. I am going to un-select quarters since I only want to see months and years. Once I make that change I click on OK and it will update my pivot table:

I know have a summary that shows total dollars split by customers and by month. But I’ve decided I do not want to see 2010. I can filter the data even though I’ve included it in my pivot table.

Filtering Data in a Pivot Table


To filter the data, I will click on the Column Labels button since I put the dates into columns. I will see a drop down option to select the field I want to filter (assuming I have more than one field).

I select the Years field and then in the list of checkboxes below I can uncheck the ones for 2010 and 
< 1/1/2010:


After updating my selections I click on OK. Now anything prior to 2011 has been removed from my pivot table. 

Changing the Formatting of Fields


My pivot table looks okay so far except that I don’t like the number formatting that is used. You could change the formatting by selecting the columns and making your changes but the problem with this is it is a temporary solution. If you refresh your pivot table at any point the formatting will go back to what it was. In order to change the formatting for a field, simply right-click on one of the data points and click on Value Field Settings

Which will bring up this pop up:
From here I click on the Number Format button. That gets me back to the Format Cells pop up:
Here I’m going to change my format to Accounting so that it has commas and dollar signs. However, you can change to whatever format you prefer. I now hit OK and get back to my pivot table which now looks like this:

Changing Pivot Table Values to Look at Averages


Currently I see the total dollar value of my sales, but what if I wanted to see the average instead? Then I can right-click on any of the dollar amounts and again go back and select Value Field Settings.

Here I can change from Sum to Average. If I click on OK now my table will show averages now:

You will notice at the top now instead of Sum of Total Dollars it now says Average of Total Dollars. This is a quick way to check what data you are in fact looking at.

Show Values as a % of a Column or Row

I’ll go through another scenario now, assuming that I don’t care about averages but instead want to see the sales as a percentage of the total month. Again I go back to Value Field Settings and change the calculation back to Sum and then click over on the tab on the right called Show Values As

In the drop down selection I select % of Column Total. The reason I select this is because I want a % of the total for the month. Since my dates (and as a result, months) are in the columns, I select columns here. After clicking OK I now see the following pivot table:

If I wanted to see what % of the customer’s sales occurred per month then I could use the % of Row Total rather than column. I am going to revert back to showing as values for the next example. To do this you can just go back to Value Field Settings and under the Show Values As tab, select No Calculation.

Adding More Fields and Changing Views to Tabular


I am going to now add another field to the rows, specifically, I am going to drag the Salesperson field over to the row columns. If you want the pivot table to first be sorted by Customer and then Sales Rep, the Sales Rep field should be dragged under the Customer field. If you want the pivot table to first sort by Sales Rep and then by Customer, make sure the Sales Rep field is dragged above the Customer field.

In my example I am going to put the Sales Rep field underneath the Customer field since I think that is a more logical hierarchy in this example. I’ve added another field, but now I get to a common pivot table problem, my table looks like this:

If I want to use the pivot table in a formula or copy it as values somewhere, then this format is not very helpful since it doesn’t have all the information on one line. I want the customer to show on every line. To do this, I go under the PivotTable Tools section again and under Options/Analyze I click on Report Layout and select Show in Tabular Form

My pivot table now looks like this:

I’ve now gotten my column back for the Customer and Sales Rep fields, but the Customer field is still showing blanks for many of the items.

Again I will go back to the Report Layout options and this time select Repeat All Item Labels

Now my pivot table looks like this:

This is an easier format to follow since it is has all the relevant data on one line and it is easier to read. If I don’t want to see all the customers and the sales rep detail, I can collapse the field by pressing on the – button next to the customer’s name at the top. This will change it to a + and collapse the field. If you want to do this for all customers, right click on any customer and select Expand/Collapse and select Collapse Entire Field.

This will now give me a tidier pivot table:

Viewing Contents That Make Up a Cell


Lastly, I will go over how to see the contents of a cell. As mentioned earlier, one of the benefits of a pivot table are that you can simply double click on a cell to see what makes up its contents.

For instance, if I clicked on the cell in the first row for $30,625, it will open a new sheet with the following in it:

What this tells me is that cell was made up only one of this one entry. If there were multiple entries for that customer, rep, month, and year, then you would see a list of all of those items. In this case it was only made up of one transaction.

conditional formatting new rule

How to Setup Conditional Formatting (including Formulas)

Conditional formatting is useful for highlighting cells or ranges if a condition is true. For example: highlighting negative values as red or positive ones as green. You can also do more complex formatting like highlighting an entire row if it meets a criteria.

Creating A New Rule

To get started with conditional formatting, you need to select new rule from the conditional formatting options which is under the Home tab:

conditional formatting new rule
You will then have quite a few options as to what you want to do:
conditional formatting all cells based on values

Format All Cells Based On Their Values

This is useful if you want to show some sort of progression from one value to the next, as one colour will fade into the next. Here is an example using this conditional formatting on a range of values from 1 to 5:
conditional formatting all cells based on values
1 started from the dark orange and gradually got to a light yellow colour by the time it got to the number 5. You can change the colours involved or even the range. You also can change the values instead of using lowest to highest values you can hardcode figures, use percentages, or whatever else. In all likelihood it will show something very similar regardless what you select, so leaving it to the default setting here (low to max) is going to be sufficient in most situations.
Another way you could use this formatting is if you wanted to compare a time-series. Assume the below are sales numbers and you wanted to highlight good and bad years. 
conditional formatting all cells based on values
You can see in this example there is no longer the smooth transition as in the prior example since I’ve assumed sales are bouncing up and down. The downside of using this type of conditional formatting as you could have a really colourful dataset if you did this. 
You may like it initially but if you are dealing with lots of data it may not be all that helpful because you are dealing with many different shades of colours now and you may find yourself comparing different shades to see if one is darker than the next. And conditional formatting is most useful when you don’t have to spend time analyzing colours, and instead the colours help you do the analysis by easily standing out and highlighting what you want to see.

Format Only Cells That Contain

The next option on the formatting rules allows you to specifically look at the cell values. Keeping my sales numbers example from above I want to highlight cell values from 200 to 500. In this case I only need to select between and a low value of 200, and a high of 500. 
Now unlike the format all cells example, the remaining conditional formatting rules require you to explicitly set what formatting you want to apply, it won’t just smooth colours from one to the other. And every cell in the range won’t have conditional formatting on it unless I explicitly state it. To set the formatting for I want cells that fall in this range to be I just click on the Format button below and apply what formatting I want. In this example I just set the cells to be highlighted in green.
conditional formatting cells between

(Note you do not need to put the = sign before the number, it automatically does this after you have already setup the conditional formatting.)
Below is my result
conditional formatting cells between
The cells that do not fall within this range do not have highlighting. If I wanted them to have highlighting I would have to change the rule, or add another rule for them. So what I will do is add another rule for any values under 200:
conditional formatting cells less than
In this case I set the formatting to be light red. 
Please note you still want to make sure your range is selected when you are adding a new rule so that it gets correctly applied to the range. Otherwise you will need to adjust the conditional formatting settings so that they are applied to the correct range. 
Both the drop downs that currently contain Cell Value and less then can be changed. Cell Value can be changed to the following:
conditional formatting cell value

If you change this value then some of the options for the next drop down will change as well. Obviously you cannot choose less then or greater than operators when dealing with text. There are a lot of possibilities here so you can experiment with them by changing these drop down selections. Currently, for the Cell Value selection, these are the different operators available:

conditional formatting operators
Going back to my example here, I selected the less than operator since I wanted to highlight values that were under 200. My result is the following output:
conditional formatting between less than
Now I have formatting for every number except 800. So I could make a similar rule and set that one to anything over 500.
The disadvantage of using the formatting based on values in the first example is that not all your values have conditional formatting on them. But this could be an advantage as well as it allows you to have more control over the exact type of formatting you want. Here I can have green and red which helps to quickly distinguish the results without having to closely look at the shading.

Format Only Top Or Bottom Ranked Values

This formatting option allows you to just highlight your top or bottom values. 
conditional formatting top ranked values
I have selected the top 10 to be highlighted in green and as a result I get the following:
conditional formatting top ranked values
The problem here is I don’t have more than 10 values so everything will be highlighted. In fact, even if I were to select bottom 10 then that would apply to everything as well.
One way around this is to check the box for % of the selected range. now it will look at the top 10% rather than just the top 10 values.
conditional formatting top ranked values
Now my highlighting looks as follows:
conditional formatting top ranked values
What this effectively does now is look at the percentiles and pulls the top 10%. So if I had a data set of 20 values, it would highlight the highest two values.

Format Only Values That Are Above Or Below Average

This option will just compare the value against the average. 
conditional formatting above below average
If I select that it highlights anything above the average I will get the following result:
conditional formatting above below average
The average for this data set is 330, so it correctly has highlighted the values 500 and 800.

Format Only Unique Or Duplicate Values

This is probably the simplest formatting option where you have only one of two options – unique or duplicate.
conditional formatting duplicate values
In my example all of my values would be highlighted since none are duplicates.

Use A Formula To Determine Which Cells To Format

This is the most versatile option for formatting. But also is the one that will take the most time to setup. In an earlier post I showed how to use this option to create highlighting on alternate rows.
In this example I’ve downloaded Alphabet’s financials from Google Finance for the past five quarters. This is what it currently looks like:
conditional formatting financials
I will start with setting up a rule to highlight every column where income after tax is more than 5,000. To do, I will select cell B11 and setup the following rule:
conditional formatting formula
I have frozen row 11 since that is where the after tax numbers are, and I want these figures to change based on what column I am in, but not what row I am in. So for that reason I am freezing the row and not the column.
I selected cell B11 when entering this formula because I wanted to be in the same column because when I go to re-size the cells that I want this formatting to apply it to, it will adjust the formula. So if I was in column C and entered my formula as above (referencing column B) then if I change the range I want to apply it to, say columns B:F rather than just the cell I was in in column C, the cell it will be evaluating now will be A11, rather than B11. It will reflect the fact that my range has changed (unless of course I wanted to freeze the column as well but that would not be helpful in this situation).
Which brings me to the next step: applying this to the relevant columns. Initially when you setup rules for conditional formatting it by default assumes you are applying them to the range you have selected. So I could have selected the range B:F but I can just go back into my conditional formatting rules and change the range:
conditional formatting manage
Now this rule will apply to all the columns from B to F. As a result, my updated data set looks as follows:
conditional formatting formula
Now every column where Income After Tax was more than 5,000 has been highlighted.
The easiest way to understand formulas in conditional formatting is this: treat it as an IF function, except start from the logical test argument and ignore the values that they will be if they are true or not. After all, if it is true, the formatting applies, if it is false, it will not apply. In the above example my IF function would have been something along the lines of this:
=IF(B$11>5000,X,Y)
Where X is the conditioning applies, and Y is that it doesn’t.
I will apply this logic to use a length (LEN) function. For no logical reason whatsoever, I am going to highlight all the rows that have descriptions in column A that are both longer than 20 characters and have a comma in them. If I were going to use an IF function, the formula would look as follows (in cell B1):
=IF(AND(LEN($A1)>20,ISNUMBER(FIND(“,”,$A1,1))),X,Y)
If I were to apply it to conditional formatting it will look as follows:
conditional formatting formula
As you can see it’s a copy and paste from my IF function, just the logical test argument. I enter this in cell B1 just to make sure the referencing doesn’t change and then I apply it to columns B:F and my data set now looks as follows:
conditional formatting formulas

Managing Multiple Formatting Rules

I have an overlap now in rows 3 and 5 as they are highlighting the areas that were previously highlighted in green. If I wanted to change this to make those back to highlight in green I can change the hierarchy of my formatting rules. I can change this by going into Conditional Formatting -> Manage Rules.
If you do not see any rules even though you have set them up, make sure at the top where it says Show formatting rules for that This Worksheet is selected. See below:
conditional formatting rules manager
If the range selected above is not correct then you may not see your conditional formatting rules. 
My rules look as follows:
conditional formatting rules manager
I can change the hierarchy by selecting the green highlighting criteria and clicking on the up arrow to move it above the yellow highlighting criteria. That will mean the green highlighting rules will be applied first. That still won’t keep it green since it just means the yellow highlighting criteria will be applied afterward. This is also the screen where you can delete any formatting you no longer want.
Instead, what needs to happen is for the Stop If True field to be ticked off for the green highlighting rules:
conditional formatting rules manager stop if true
Now the green highlighting is first and if the condition is met the yellow highlighting rules will not run. Now my data set looks as follows:
conditional formatting formulas table
The yellow highlighting rules have now only been applied to the columns where the green highlighting did was not. By using the Stop If True and setting your hierarchy for formatting you can prioritize what formatting you want to be applied.
RAND Functions

Use Excel’s Random Number Generators to Populate Sample Data

Excel has random number generator functions which are useful if you need to test a template or create some sample / dummy data.

The RAND function returns a number between 0 and 1. You could multiply this by a factor of 10 or 100 to get a much larger number if you need it. Once you are happy with the data that has been populated then you will want to copy and paste it as values otherwise the numbers will change every time a recalculation occurs. This is true of any random number function in Excel.
Similarly, the RANDBETWEEN function will return a random  number between a range that you specify. In the below example I use 1 and 100:
RAND Functions
If I used the RAND function and just multiplied by 100 I could get similar results to RANDBETWEEN. The latter just saves you that extra step by being able to specify your parameters right in the formula. It allows you also to be more specific (say for example I wanted a random number between 1 and 35, the RANDBETWEEN function would certainly be easier to use)
If you do not like the random numbers you have generated, you can simply just hit the delete key on an empty cell and your random numbers will be regenerated. If you don’t want your numbers to change anymore then you will want to copy them and paste as values.
To use this to create sample data I am going to make two lists, one for employees, one for stores. From there, I can use the RANDBETWEEN function in conjunction with the INDEX function to extract values from the lists:
RANDBETWEEN Function
In columns A and B I am just using the RANDBETWEEN function to select a number between 1 and 5, as that is the number of different employees and stores I have listed in columns C and D. In columns E and F I use the INDEX function to extract from those lists using the random numbers generated in columns A and B.
I will break down the INDEX formula in column E a little bit here:
=INDEX($C$2:$C$6
In the first argument I am selecting my employee list since this is where I want the result to come from. I also lock the cells using the $ sign to ensure that as I copy the formula down that range is locked and will always reference C2:C6.
=INDEX($C$2:$C$6,A3
The second argument in the INDEX function is the row number from where I want to pull my value. Cell A3 is my first random value – which in this case is 4. This means that from the Employee List range (C2:C6) I want the value on the fourth row of that range – which is not row 4, it is row 5 since I start counting from the start of the range, which is on row 2. As I copy this formula down the row number will change to the corresponding value in column A. 
Because the lists only have one column you could actually stop here.
=INDEX($C$2:$C$6,A3, 1)
The last argument in the INDEX function is the column number. In this example I only have 1 column in my lists so the value is equal to 1. You could skip this argument and it will still work however it’s good habit to always enter the column number.
The above formula tells me to look at range C2:C6, and extract the value from the row that is referenced on A3 (which is 4), and from column 1. That point of intersection is Employee 4, since it is on the fourth line of that range, and in the first and only column.
If I copy the formula down a cell it will do the same except this time pull the value from the row referenced in cell A4 – this time it is 2. As a result, my result is Employee 2 since that value is on the second row of the range and again in the first column. 
Column F is the same formula as column E except this time it references the store list (column D) and the second RANDBETWEEN column (column B). This is just to show you how you might fill in multiple items. You wouldn’t want to use column A again otherwise you are guaranteed that the same row will be returned and you will always have Employee 4 tied to Store 4, and thus, not truly random combinations. 
In this example I have my lists, random numbers, and results all in the same area for illustrative purposes but they do not need to be even on the same sheet.
goal seek inputs

Use Goal Seek to Avoid Trial and Error

Excel’s what-if-analysis options aren’t the greatest, but one option which is very useful and can save time is Goal Seek. What Goal Seek effectively does is it can do trial and error for you in seconds, and be much more precise than doing it manually.

In many examples you could use algebra to get to your answer but if it’s a one-time calculation perhaps it’s not worth the trouble of going that route. For example, suppose I had an investment of $1,234 and wanted to know what average return would be needed for it to grow to $10,000 after 10 years. This can certainly be done with algebra, but Goal Seek can also do it. Below, I have entered my inputs in yellow.
goal seek inputs
My ending balance is equal to the following formula : =C4*(1+C5)^C6
C4 = Investment
C5 = Required Rate of Return
C6 = Years
Next, I will select Goal Seek from the Data tab under What-If Analysis

what if analysis
The next screen prompts me to enter which cell I want to set to which value, and which cell I want changed to accomplish this (the variable). In my example I use C7 for the set cell box (this is my desired ending balance), the value is 10,000, and by changing cell I enter my required rate of return (which is C5).
goal seek function
After clicking OK it fills in the required rate of return as 0.23273147. It also sets the value in C5 to this amount as well. If I hit OK on the next screen the value in C5 will keep this value, if I cancel, it will revert to what was there before – nothing.
goal seek analysis
This was a simple example but there are also more complex ones it can be used for. Another example could be an amortization table. The table below has a principal amount owing of 10,000, an interest rate of 5%, a payment of 550 per month. This is what it looks like:
goal seek amortization table

So it will take roughly 19 payments to for this amount to be paid off under the terms described. But let’s say I wanted to know precisely what interest rate would have to be applied to make the ending balance at payment number 20 equal 0. I don’t want an extra payment for the balance to be included at the end, just so that it perfectly matches 0 after payment 20.

To do this, I go back to Goal Seek. For the first box (set cell) which is what I want my result to be – I select the ending balance at payment 20 – currently it is (579.34). This is cell F27 on my spreadsheet. The second box is what result I want, which in this case is 0. Lastly, my variable, or the cell I want changed to make this work – that is my interest rate, which is in cell C4 – currently it is at 5%. These are what my inputs look like:

goal seek interest rate

Once I hit OK, I get the following:
goal seek amortzation table
You’ll notice now my target value matches my current value and the Goal Seek was successful. It has changed my interest rate to 11% – not exactly 11%, 11.1048262490731%. A very precise percentage that has allowed my table to reach 0. 
Goal Seek can be used in a variety of different circumstances as you can see. As long as you have a formula like in the above two cases that the change cell (e.g. variable) directly effects, then it should work. I could have selected the payment as my change cell rather than the interest rate and my result would be as follows:
goal seek amortization table
It would indicate a rounded payment of 522.16 (exact amount of 522.162995552194) would be needed to pay off after 20 payments with the interest rate staying at 5%.
The one limitation of Goal Seek is that you can only use one variable. But beyond that it does it very well and can help save you a lot of time.
date function

Date Calculations and Formatting

In this post I am going to cover some commonly used date functions used for calculating differences in dates.

INCREMENTING DATES BY DAYS AND MONTHS


If you want to increment a date by months, years, or days, then you can use the DATE function to do so. This function has three arguments: year, month, and date. If I have a date in cell C3 and I want to make cell C4 one month later, I would use the following formula:
=DATE(YEAR(C3),MONTH(C3)+1,DAY(C3))

Below is an example of the daily increment (no formula necessary) where I just enter the first date and autofill the rest of the dates down, and a column with a monthly increment that uses the date function:

date function

For each of the year, month, and day arguments I used their individual functions. The YEAR function will take the year of the date specified, as the MONTH function will take the month, and the DAY function take specific day. For the DAY and YEAR functions I simply just referenced cell C3 since I want those values to remain the same. The MONTH function also took the same value from cell C3 however I added +1 to it as well to increment the value by one month.
If I were to copy this formula down it will continue to increment by month. So if I wanted my dates broken down by month this accomplishes that. Now, there is another way, even simpler way, just by using Excel’s autofill feature. If I enter my starting date in cell B3 and pull down using the fill handle, Excel will increment by day.

However, if I select cell B3 and pull down the fill handle while also holding down the right click button, it gives me the option to select how I want to increment – including fill days, weekdays, months, and years. 
This way you can ensure Excel increments as you like. If you just use the fill handle without the right click button you don’t get these options and Excel fills how it thinks make sense. Interesting to note that if you fill in cell B4 with the next month, and then select cells B3:B4 and then use the fill handle, then Excel has figured out you want to increment by months and doesn’t increment by days anymore. The more of a pattern you show Excel, the more likely it will know what you are trying to do.

incrementing dates

In the above picture if I now double click the fill handle my daily increment will become monthly since I have selected two points in my series. And since the two points have a monthly interval, Excel assumes I want the rest of the series to also have monthly intervals as well. The result becomes this:

incrementing dates

You may wonder what the point would be of using the date function in the formula above if you can just use the fill handle since it is faster and easier. The main benefit of using a formula is if you don’t want to have to use the fill handle all the time. If you just need to setup your dates one time, then certainly the fill handle makes sense. But if you are working with a large data set that you will continually add to, you might find it a bit easier to have a formula there as opposed to using the fill handle each time and making sure it is incrementing correctly.

CALCULATING THE DIFFERENCE FROM/TO TODAY

If you want to include today’s date in your calculations, you can use the TODAY() or NOW()functions. These functions have no arguments and you just enter them with both parentheses. This will generate today’s date. The difference in the two functions is the NOW() function also includes the time. But for the purpose of calculating the difference in days, either one will do the job.

NOW() and TODAY() Functions

Alternatively, you can use the shortcut CTRL+; which will plug in today’s date. If you want the date to update every time you open the spreadsheet then you are better off using the functions. If you want to do a one-time insertion of the current date never to change later, then the shortcut will do. It is no different than just entering the date yourself.

END OF MONTH

Suppose I wanted to not calculate the number of days from today, but the end of the month. What I could use is the EOMONTH function. This has two arguments – start date, and months. If I wanted to use the end of the current month, I would enter the following formula:
=EOMONTH(TODAY(),0)
If I wanted the end of next month, then I would change the 0 to a 1:
=EOMONTH(TODAY(),1)
The months argument just tells Excel how many months ahead to go. Whether you change the months argument or alter the start date to get to a later month end value, it doesn’t matter, both methods can get you to the desired result.

EOMONTH Function

CALCULATING WORKDAYS

Up until now I’ve gone over how to calculate the difference in days. But suppose I wanted to calculate only working days.
To do this you can use the NETWORKDAYS function which will take the starting date, ending date, and calculate how many work days fall in between. To be more accurate you could also include a list of holidays into the function (which you will have to populate).

NETWORKDAYS Function

In the above example I selected Jan 2 and 3rd as holidays so as a result the NETWORKDAYS function with holidays has two days fewer than the function without holidays since those days both fall within the date range. Without a list of holidays, the NETWORKDAYS function effectively calculates weekdays since it would only ignore weekends. Also note that in the argument for holidays, do not include any text fields such as a header for holidays. If my range included the header then it would return a #VALUE error.

A similar function, WORKDAY takes the starting date, and you select how many work days you want to advance by, and it will return a date result for the next working day after the days you specified. Again, you can list the holidays to ensure a more accurate calculation.

WORKDAY Function

If you don’t use holidays then both functions (NETWORKDAYS and WORKDAY) will not adjust for them. However both functions will recognize and skip over weekends and so if you leave holidays blank they effectively just look at every weekday.

CALCULATING WEEKS, MONTHS, YEARS

If I wanted to calculate the difference between two dates in weeks, what I could do is use the WEEKNUM function and then use my date as the argument for that function. Once I calculate this for both dates then I just calculate the difference. This strictly looks at the week of the year, so it won’t take into account whether one date is a Friday and the other a Monday.

WEEKNUM Function

And if you wanted to you would be better off dividing the difference in dates by 7 to get fractional weeks.

For months you can do the same thing, except using the MONTHfunction which will yield a result from 1 to 12. Note the month difference in this example will only work if you are in the same calendar year. For example January will always yield a month of 1 so it won’t be helpful for these purposes. To more accurately track the number of months when spanning different years you would need to incorporate a calculation for years.

The YEAR function will do the same as the month function, give you the year of the specified date, which you then can use in your calculations to calculate the change in years.

You can also use the YEARFRAC function if you wanted to calculate the difference in fractions of a year, to give you a more specific result when calculating the difference in years.

YEARFRAC Function

FORMATTING DATES

You can format dates so they display exactly how you like. The easiest way to do that is go to the cell formatting (right click format cells, or click ctrl+1), and select the CUSTOM category.

Custom Date Formatting

If you want month/day/year (which it may already be set to) the formatting is just m/d/yyyy. But what if I didn’t want to show all four numbers for the year, just the last two? Then I would set it to m/d/yy. You can flip the d and the m around so it is d/m instead of m/d and then you have day/month/year. If you use two d’s rather than one you will get a 0 if you are in single digits for the day. For instance instead of 1/1  you could get 01/01. You will notice the changes in the sample box above as you change your custom category.

Here is  a quick summary of the use of letters and what results they will yield:

single m or d = will not use 01
dd or mm = will use 01,02,03…
ddd or mmm = will spell out the abbreviation (e.g. Mon for Monday, or Nov for November)
dddd or mmmm = will spell out the entire day or month (e.g. Monday, November)
y or yy = will use the last two digits of the year
yyy or anything more = will use the full four digits of the year
If I wanted to use a formal date that showed the day and month spelled out, followed by the numerical day, a comma and the year, I would use the following format:
dddd mmmm d, yyy

Date Format
You can use hyphens, backslashes, commas, or any other punctuation to change the appearance just to what you like.

horizontal line data

Plotting Vertical and Horizontal Lines in Charts

When charting something on Excel you sometimes may want to add vertical lines to identify key dates, or horizontal lines for target or benchmark amounts. In this post I’ll show you how to do both.
Horizontal lines can be used to identify target or benchmark amounts while vertical lines are useful to mark dates. In my example I’ve downloaded googles historical closing stock prices for the year and I’m going to mark the year long average and identify their earnings dates

Horizontal Lines

I’ll first start with horizontal lines. If you have amounts on the y-axis then a horizontal line can act as an indicator to show if the amounts have crossed a targeted amount. In order to add a horizontal line all you’ll need to do is create another column in your table.
The key thing for this column is all the values have to be identical for each entry. I can set it to a single number and copy it down, or I can do a calculation as well. What I will use is the closing price average in this column using the AVERAGE formula. If I copy this down I am left with a value that stays constant for the entire data set.
horizontal line data
If I chart this graph using a line chart my horizontal line is now visible
chart horizontal line

Vertical Lines 

Vertical lines are a bit more tricky but not difficult. In my example I am going to put a vertical line at every earnings date since I know those days will have a lot of fluctuations and will also create quarterly intervals. I have created a column for all the earnings dates in the year, aka my earnings calendar.
earnings calendar
 Again I will need to create another column in my table. 
In this column I will look to see if the date for this row matches one of the dates in my earnings calendar. If there is a match, I will set the value to 1. Whether you use the MATCH formula or the VLOOKUP formula doesn’t matter. But I will use the MATCH formula in this example. The key is including the IF and ISERROR formulas  because if I do not find a value it will return an error, and specifically I will use the NA() formula to return the NA error so that that amount does not appear on the chart as a zero. And if it is a match, I can make it set to 1.
First I will start with the IF function, add ISERROR, and add the MATCH function to now see if the date on this row matches anything in my earnings calendar), and if it is not a match, make it equal to an NA error, otherwise make it equal to 1 indicate a match.
My formula looks like this:
=IF(ISERROR(MATCH(A2,G:G,0)),NA(),1)

Where column G is where my earnings calendar is located.
Now I will copy my formula all the way down. It may not look terribly nice in your table with all those errors but it will get the job done.
Match function
I’m ready to chart my graph now. Select line chart again. However unlike for the horizontal line, it needs some work.
The earnings dates need to be plotted against the secondary axis. To do this, I right click on the series and click Format Data Series and select Secondary Axis.

Format data series

Next, I need to change the series chart type so that it is a column chart. I will right click on the series again and select Change Series Chart Type and then select a column chart.
column chart
I will go back to format the data series once more to add a border which will allow me to make the line look thicker.  By going to Border Colors this time I can specify the colour, and then under Border Styles I can modify the Weight.
I will now format the secondary data axis. I will set the maximum height equal to 1, the value that I set for the earnings column when there was a match. It doesn’t matter what you set that value to, whether it is 1, 100, 1000, you will just need to change the axis accordingly to make sure that value is at least as large as your scale to have the line go all the way across. If you have values that you are currently using on your secondary axis you will want to take that into account and consider what is a good maximum value for that secondary chart, and use that value, rather than 1.
Next, I will hide the secondary axis (if you need it for other values obviously this is optional). To do this right click on the axis, click Format Axis, and set Axis Labelsto None.
Axis Labels
Now my chart is good to go with both vertical and horizontal lines.
Chart Vertical Line

Create Drop Down Options in Excel Using Data Validation

You can easily create drop down options in Excel by just using data validation rules. First, what you need to do is create a list of all the values you want in the drop down list. Once completed, assign a named range to those values (on how to assign a named range, refer to this post):

Note with named ranges you cannot use spaces. If you need to, use an underscore.

Next, select the cell where you want the drop down list to be. Under the Data tab in  Excel select Data Validation. Under the allow section, select List. Then in the source section reference your named range with an = sign before it. See example below:

If you didn’t assign a named range to your options, you have to specify the cells here. Click ok and you’re done.

Now when you select that cell you will see the drop down options available. If you want to copy the drop downs to be available to adjacent cells use flash fill by dragging the bottom right corner of the cell with data validation and drag over to which cells you want it to apply to.

Drop Down Options Based on Previous Selections

What you can also do is make your drop down options dependent on what you selected in a prior drop down selection. This requires using the INDIRECT function.

In the first drop down option what I will do is create a named range for all the categories,Category1-Category5. Once a user selects an option, the next drop down will be based on the products relating to the category that they have selected. The first part is the same as the process mentioned above, only now it is relating to the selection of a category group. The key here is making sure that the categories are the same as the named ranges I have created for them. For example, if a user selects Category1, in order for my other drop down to work I need to have a named range assigned to Category1. If I do, then I can utilize the indirect formula. Below is the data validation I would use for the second drop down option:

My category selection is in cell B7. The indirect function looks at cell B7 to see what range I am referencing. If I select Category1 in cell B7, then the data validation will pull the named range for Category1. If I haven’t set up a named range for the selection, the drop down list will be empty. Otherwise, my second drop down will now reference the products in the named range belonging to Category1.