Excel Tip: Remove old items from PivotTable drop-down lists

Just a short post this week on something I only discovered myself the other day.

Before we get into the post though, just a quick reminder that Mynda Treacy's Dashboards Course will be closing on Thursday, so if you haven't already purchased it and don't want to miss out, get yourself over there now, before it's too late - you will also receive a free copy of my Introduction to PivotTables course. You won't regret it.

This week's post was prompted by a question from a client who was baffled by items appearing in the drop-down selection boxes in his PivotTables that were no longer in the data being referenced.

This was a particular problem as the PivotTables were being sent out to individual salespeople reporting on their own data - but because the pivot tables had previously been looking at the whole data set, all of the other salespeople's customers were appearing in the drop-down lists.

I wasn't quite sure myself why this was happening at first, but going through the PivotTable options (accessed by right-clicking on the PivotTable and selecting PivotTable Options...), I discovered the following setting on the Data tab:

Retain items deleted from the data source...Number of items to retain per field

This setting is by default set to Automatic and changing it to None will stop the PivotTable retaining these items in the drop-down lists (they will disappear the next time you click Refresh). It's as simple as that - once you know that the setting is there!

Post Script

Out of curiosity, I tried to find out what the three possible settings are supposed to do (the third setting is Max). Microsoft Excel help was not particularly enlightening on the subject:

Retain items deleted from the data source section
Number of items to return per field    To specify the number of items for each field to temporarily cache with the workbook, select one of the following:

Automatic     The default number of unique items for each field.
None     No unique items for each field.
Max     The maximum number of unique items for each field. You can specify up to 1,048,576 items.

It does not give any indication what the default number is or how you specify the maximum!

A search around the web didn't bring up any further clarification, but I'm sure some of our clever readers can elaborate, so please add a comment if you know what the difference is between the Automatic and Max settings!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Guest Post - Calculating Median and Mode in Excel

This week you've got a break from me with a very useful guest post from Alan Murray of Computergaga.

Before we get into that though, just a quick reminder that the early bird discount on Mynda Treacy's excellent Excel Dashboards course expires on Thursday, so if you don't want to miss out, click over there now.

OK, on with Alan's tips for when you don't just want the average AVERAGE!

Calculating Median and Mode in Excel

When calculating averages in Excel, this normally involves using the AVERAGE function. The AVERAGE function in Excel calculates the mean of a set of numbers.

There are two other types of averages known as median and mode. Let’s take a look at calculating median and mode in Excel.

Calculating the Median

The median is the middle number in a sorted list of numbers. If you were to calculate the median with a pen and paper you would have to;

1. Write down the list of numbers in numerical order.
2. If there were an odd number of results, then the median would be the middle number.
3. If there were an even number of results, then the median would be the mean of the two central numbers.

Fortunately Excel can handle all of this for us with the MEDIAN function. The following formula can be written to calculate the median from the list of exam scores.

=MEDIAN(B4:B13)

The list of exam scores is not sorted in numerical order, but that is no problem for Excel. The median has been found by finding the mean of 55 and 65 because there are 10 scores which is an even number of results.

39, 48, 51, 52, 55, 65, 75, 77, 77,90

(55 + 65) / 2 = 60

Finding the Mode

The mode, or modal number, is the number that occurs most frequently in a list. There can be more than one mode.

In Excel, the MODE function can be used on a list to return the number that occurs most often. In this example the formula would look like below.

=MODE(B4:B13)

The mode in this example can easily be seen to be 77 as it is the only one to appear more than once. If there is more than one modal number, this formula will only return the first one.

Returning Multiple Modes

To return multiple modes the MODE.MULT function can be used. This function was released with Excel 2010 so is not available on versions previous to that.

This function is an array function, so to run the function you should press Ctrl + Shift + Enter instead of only Enter. It will also appear in the Formula Bar wrapped within curly braces.

You will need to select a range of cells to apply the function to, or copy the formula and run it again.

In the example below there are 3 modal values. The following MODE.MULT function has been used in cells D5:D7 to return the results.

{=MODE.MULT(B4:B18)}

When there are no more modal values the function will return the #N/A error.

Watch the Video

Create a Frequency Distribution Table

Results are often broken down into groups, or classes. This frequency distribution table gives us a good view of the spread of our data and also identifies the class with the most occurrences, known as the modal class.

The table below shows the grades of 34 pupils in range C4:C37.

The COUNTIFS function has been used to calculate these frequencies. The formula below was entered into cell F4 and then copied to the other cells of the table, with the criteria altered to match the class.

The formula counts the number of scores that are greater than or equals to 31, but less than 40. This is then repeated for each class.

The COUNTIFS function was released with Excel 2007. If you are using a version previous to this, the SUMPRODUCT function could be used to count based on multiple conditions.

.

About the author: Alan Murray is an IT Trainer and the founder of Computergaga. Author of the popular Computergaga Blog providing the latest Excel, Word, PowerPoint and Project tips and techniques.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Dashboards Online Course - Limited time offer

A number of you will remember that I have previously recommended Mynda Treacy’s Excel Dashboard course (many of you purchased it), and I'm pleased to tell you that it is once again available - for a limited time.

The course is video based and available online 24/7. It comes with comprehensive Excel workbooks and several sample dashboards to keep. There’s also an option to download the videos, plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

Dashboards are an incredibly valuable tool in today's market for consultants, analysts, managers and accountants. But Excel doesn’t make it straightforward to build highly professional and interactive dashboards. That's why this type of training is crucial.

What readers are saying about the course: 
The previous classes have been a huge hit with many people saying how they love the cool
techniques and how they've been able to impress their colleagues and clients by using them in all
sorts of reports, not just dashboards.

Others have said the course has prompted them to take a whole new approach to producing their monthly reports.

I highly recommend the course but don’t take my word for it. You can read comments from past students and find out more (including sample videos) here.

Bonus 20% Off (and a free gift)
If you join the class by January 23rd you can get it for 20% off plus I’ll include my Introduction to Pivot Tables video training course absolutely free, just enter FEECHAN in the ‘Referral Source’ field below the PayPal Buy Now button when you purchase Mynda's course and then email me your receipt.

So, do yourself a favour and check out the course. The price is incredibly fair, the course is excellent and it’ll transform your Excel reports and possibly even your Excel career.



Disclosure: I make a small commission for students who join Mynda’s course, but as you know I 
don’t just recommend anything and everything. It has to be of outstanding quality and value, and 
something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform 
your career, you have to actually put it into practice, but then you know that. 



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: The structure of every Excel function

As this is my first post of 2014, I must start by wishing all of my readers a very Happy New Year, and thank you all for making 2013 the most successful yet for Not Just Numbers.

I have explained hundreds of Excel functions on this blog, and, in one post, covered how to access every function in Excel, but it occurred to me that I have never covered the universal way that every Excel function works, so I thought that would be a useful way to start the New Year.

Every function in Excel follows exactly the same format, and when you understand that, it makes it much easier to pick up new functions.

The basic format of an Excel function is the name of the function, followed by its arguments in brackets and separated by commas:

=FUNCTIONNAME(argument1,argument2...)

I have included the equals sign (=) in front of the format above but this is not strictly part of the individual function. The equals sign (as the first character in a cell) tells Excel that the contents of that cell are to be evaluated. So, if the function was entered in a cell on its own, it would need the equals sign - however if the function was used within another function or calculation, you would not repeat the equals sign.

The arguments of the function are the values that it needs to know in order to calculate. The number of arguments will differ depending on the function. Some functions also have optional arguments (in the Excel help files, these are shown in square brackets.

Let's take the IF function as an example. In programming, an IF statement would normally follow the following syntax:

IF logical_test THEN value_if_true ELSE value_if_false

However, in Excel, it must follow the Excel format for all functions as described above, so the three arguments are separated by commas in brackets after the function name, i.e.

IF(logical_test,value_if_true,value_if_false)

Its format, according to the Excel help files, is:

IF(logical_test, [value_if_true], [value_if_false])

As we can see, only the first argument is required (logical_test) the other two being optional (value_if_true and value_if_false).

What is acceptable for each argument will depend on the function in question, but in this case, logical_test is an expression that can be evaluated as true or false, whereas value_if_true and value_if_false can be any value (numbers or text) or any calculation resulting in a value.

So some acceptable IF statements could be:

=IF(A1=10)   returns TRUE if A1=10, or FALSE otherwise
=IF(A1=10,"Yes","No")   returns Yes if A1=10, or No otherwise

or even,

=IF(A1=10,"Yes",IF(B1=5,"Yes","No"))   returns Yes if A1=10, but if A1 doesn't equal 10,  it returns Yes if B1 is 5 but No otherwise

Yes, functions can be used as arguments for other functions (including other instances of the same function - as in this example).

The last example is a Nested IF statement. Don't worry if you haven't followed fully how the IF statement works, as this was not the point of this post. You can read more about the IF statement here.

The important thing is that you understand the structure of Excel functions in general.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".