|
|
In Excel 2007 and Excel 2010, you can use the new COUNTIFS function to count, based on multiple criteria. For example, in a list of orders, you can find out how many orders were for pens, and had a quantity of 10 or more.
I have updated the Contextures COUNT Functions page, to include a COUNTIFS example, and video demo. If you are using Excel 2003, or earlier versions, you can use the SUMPRODUCT function instead. There is an example for that function on the COUNT page too.
Watch the COUNTIFS Video
To see the steps for using the COUNTIFS function, you can watch this short Excel video tutorial.
_________
Continue reading Use Excel COUNTIFS to Count With Multiple Criteria
Have you ever written an Excel proficiency exam? Maybe you'll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam. He's having trouble with the macros and custom functions that will be part of the test.
It's been a long time since I wrote the Excel Expert exam, that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I'm remembering correctly!)
Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it's pinned to the wall, behind the door, but it's still in good shape! Wow, June 1999 – that was a long time ago.
The Excel Expert Test
The Microsoft website has a list
Continue reading Preparing for an Excel Expert Exam
To focus on specific data in a pivot table, you can use report filters or field filters.
However, using those filters affects the pivot table subtotals and grand totals. Only the amounts for the filtered data are included in the results.
Collapse or Expand the Fields
Another option for hiding the pivot table details is to collapse one or more of the items, so only its subtotal is showing. In the screenshot below, I'm about to click on the Collapse button for the Bars category in the pivot table.
The Bars data is still in the pivot table, but only its subtotal is showing, not the City detail rows. The subtotals for East and Bars are not affected.
Show the Details Again
After you're finished focusing on the specific data, you can show all the detail rows again.
A quick way to show
Continue reading Hide Pivot Table Detail Without Filtering
Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.
When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.
Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn't in my original file. That can cause problems if you're using Excel VBA to add data to the first blank row on the worksheet.
Change the Last Row Code
In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument
Continue reading Excel UserForm Data Entry Update
You know how tough it can be to maintain two versions of the same file. It creates twice as much work for you, and no extra rewards!
So, instead of having two versions of the Excel weight loss tracker – pounds and kilograms – I've rolled them into one workbook. For now, there is a separate file for the stone measurements, because it has a different layout on the data entry sheet. If possible, I'll roll that version in later.
Select a Measurement System
When you open the Excel weight loss tracker, go to the WeightGoals sheet and select a measurement system – pounds or kilos – from the data validation drop down.
That changes the labels and calculations in the worksheet. Since we've selected Pounds in this example, the labels say "Lbs to Lose" and "Height (Inches)". Those would show "Kgs to Lose" and "Height (cms") if
Continue reading Updated Excel Weight Loss Tracker
To make it easier for people to enter data, you can create drop down lists on an Excel worksheet.
Usually the source lists are stored in the same workbook as the drop downs. However, with named ranges, it is possible to use a list in a different workbook.
In the screen shot shown below, the original list is in the workbook at the left. The drop downs are in a different workbook, on the right.
There Is a Catch
My preference would be to keep the lists and drop downs in the same workbook, but if you need to have them in separate files, this technique will allow you to do that.
There's one catch though, when using this data validation technique. The source workbook, which contains the original list, must also be open, when you are using the drop down lists.
So, it's not a perfect solution,
Continue reading Excel Drop Down From List in Different Workbook
Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.
Change All Pivot Table Filters
Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.
For example, if you change the "Item" report filter in one pivot table, all the other pivot tables with an "Item" filter will change. They get the same report filter settings that were in the pivot table that you changed.
Select Multiple Items
In this version of the sample file, the "Select Multiple Items" setting
Continue reading Change All Pivot Tables With One Selection
|
|
Recent Comments