Beginning PivotTables in Excel 2007will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
How can you make it easy for people to enter and edit data in Excel, but keep them away from the data storage worksheet?
Last year, I posted a Worksheet Data Entry Form in Excel, where users could enter and view Excel data. It was based on a worksheet data entry form that Dave Peterson created.
I’ve created a new version, where users can enter, view and edit the Excel data.
Version 1: Add New Records
In Dave’s original worksheet data entry form, users could add records on the data entry worksheet, and click a button to go to the database sheet, and review or edit the order records.
Version 2: View Existing Records
In version 2, I added a few buttons to Dave’s workbook, to allow users to scroll through the existing records. With the navigation buttons, you could go to the first, previous, next or last record, or type a record number, to go to a specific record.
Version 3: Update Existing Records
In the latest version of the Excel Worksheet Data Entry form, I’ve added an update feature. As in the previous version, there are data validation drop down lists, to select Item and Location. The Price calculation is based on a VLOOKUP formula, and the Total formula multiplies the quantity by the price.
After you select a record, you can change its data, then click the Update button to copy those changes to the database.
For example, in the record shown above, if you discovered that there was an error, you could change the quantity from 500 to 200. The Total formula would automatically recalculate, to show the new total of $200.00.
Then, click the Update button, and the revised quantity and total would appear in that record on the database sheet.
The Update Code
Before updating the database record, the Update code checks to see of all the data entry cells are filled in. If they aren’t, a warning message appears, and the macro stops running. This prevents you from accidentally overwriting an existing record with blank cells.
If all the data entry cells are filled in, the code:
writes the current date and time in the applicable row of the database
adds the User Name from the Excel application
copies the data to the database
clears the data entry cells
Then, with a cleared data entry sheet, you can go on to add, view and edit other records, or save and close the workbook.
An Excel workbook certainly isn’t Fort Knox, and the information you store there isn’t too secure. If someone opens your Excel workbook, and is determined to see everything in there, they’ll probably be able to.
However, if your goal is simply to make a workbook easier for people to use, you can hide some of the worksheets, so users don’t accidentally change their contents.
For example, if your data entry worksheet has data validation drop downs, you can store the lists on a different sheet, and hide that sheet.
Hide an Excel Worksheet
To quickly hide a worksheet in Excel 2007, right-click on the sheet tab, and click Hide.
If you’re using an earlier version of Excel, activate the sheet that you want to hide. Then, click the Format menu, then click Sheet, and click Hide.
Show an Excel Worksheet
To show the hidden sheet again, right-click any sheet tab, then click Unhide. (In earlier versions of Excel, click the Format menu, then click Sheet, and click Unhide.)
In the Unhide dialog box, click on a sheet name, and click OK.
Really, Really Hide an Excel Worksheet
If you want to hide a worksheet a little better, you can use a special technique that keeps it from appearing in the Unhide list.
You know how to sort an Excel list alphabetically, and with Excel 2007 you can even sort an Excel list by colour. Did you know that you can also create a custom list in Excel and use that to sort your data, instead of sorting in alphabetical or numerical order?
Instead of sorting the products in this table alphabetically, we’ll create a custom list of products, and use it when sorting the list.
Create a Custom List in Excel
You can create a custom list in Excel by importing a list from a worksheet, or by typing a new list. In this example, there is a worksheet named Lists, and it contains a product list. We’ll import that list, to create the custom list.
To create the custom list:
Select the cells that contain the list items
On the Ribbon, click the Office Button, then click Excel Options.
In the Popular category, click Edit Custom Lists
In the Custom Lists dialog box, the list address — $A$2:$A$5 — should appear in the Import range box. If not, you can click in the Import range box, and type a range, or select a range on the worksheet.
To add the selected range as a custom list, click the Import button.
The list items will appear in the List entries section of the Custom List dialog box, and at the end of the list of existing Custom Lists.
Click OK to close the Custom Lists dialog box, and click OK to close the Excel Options window.
Use the Custom List
You can use the custom lists when sorting, and you can also use them with the AutoFill feature. Type any item from a custom list in a cell, then use the Fill handle to complete the list.
Sort the Excel List in Custom Order
To sort your list based on your custom list, follow these steps:
Select a cell in the table that you want to sort.
On the Ribbon’s Data tab, click Sort
In the Sort dialog box, select a Column from the first drop down, and select Values from the Sort On drop down.
In the Order drop down, click Custom List
In the Custom List dialog box, select your custom list, and click OK
Click OK to close the Sort dialog box
The list is sorted in the order of the items in your custom list.
Watch the Excel Sort Video
To see the steps for adding an Excel Custom List, then sorting by that Custom List, watch this short Excel video tutorial.
More Sort Options
After I wrote about sorting by colour, Jim Cone offered me a copy of this Special Sort Excel add-in. I finally had a chance to test it, and although I’m still not a fan of sorting by colour, the add-in has several sort options that would be useful. Instead of spending time on workarounds and formulas in helper columns, you can use the add-in. For example, you can:
sort a list by the length of the text in the cells
ignore leading articles (A, An, The)
sort based on the reverse order of the cell contents
sort by numbers at the end of the cell text
To buy the Special Sort add-in ($19 US), or request a trial version, contact Jim: james.cone @ comcast.net
While working on a client’s sales plan last week, I had to count the orders for a couple of specific customers. Here’s a screenshot of some sample data, with the customer names in column B.
Count cells that are an exact match
In Excel, you can use the COUNTIF function to count cells that meet a specific criterion. For example, you can use this formula to count the orders that were placed by the customer, BigMart.
=COUNTIF($B$2:$B$14,"BigMart")
The first argument, $B$2:$B$14, is the range that contains the cells to be counted.
The second argument, “BigMart”, is the value that we want to count.
There are 3 cells that contain the exact text, “BigMart”, so the COUNTIF formula returns 3.
Use a cell reference in COUNTIF
Instead of typing the text in the COUNTIF formula, you can refer to a cell that contains the text you want to count. For example, if cell H1 contains the customer name, BigMart, use this formula to count their orders.
=COUNTIF($B$2:$B$14, H1)
The COUNTIF formula returns 3, the same result that you got by typing the text in the formula.
Count non-exact matches
In the sales plan data that I worked on, there were a few variations on some customer spellings, and I wanted to count all of them. In the screenshot at the start of this article, you can see that BigMart is also listed as Big-Mart and Big Mart.
Use the * wildcard
To count all the variations, you can use the wildcard character, *, to represent any characters, or no characters, in the text string. For example, if cell H1 contains the customer name with a wildcard, Big*Mart, use this formula to count all the variations.
=COUNTIF($B$2:$B$14, H1)
The COUNTIF formula returns 7, because it counts BigMart, Big-Mart and Big Mart.
Use the ? wildcard
If you use the ? wildcard character instead of the * wildcard character, it represents one character.
Now the COUNTIF formula returns 4, because it counts Big-Mart and Big Mart, but not BigMart. It doesn’t count BigMart, because there isn’t a character between the g and the M.
More COUNTIF Examples
For a few more examples of counting cells with specific text, you can visit the Excel Count Functions page on the Contextures website.
If you’re having a party this weekend, you can use Excel to plan the guest seating. And if you sent me an invitation, it hasn’t arrived yet!
In this example, you’ll enter the guest names on an Excel worksheet, then fill the tables by selecting names from data validation drop down lists. After you’ve selected a name, it will disappear from the drop down lists, so you can’t accidentally assign one of the guests to two different seats.
Enter the Guest Names
On a worksheet named Lists, the guest names are typed in column B. Sort the names in alphabetical order, so the names will be easier to find in the data validation drop down lists.
Draw the Tables and Chairs
On a sheet named TablePlan, use the Excel drawing tools to create the tables and chairs. I used the Oval shape to draw 3 tables, with 8 chairs at each table.
Tip: To draw a circle, hold the Shift key while you use the Oval shape drawing tool.
You can number and colour code the tables, to make it easier to keep track of things.
List the Tables and Seats
To the left of the table diagram, create a list of the tables and seat numbers. If you colour coded the tables, use the same colours in the table list.
In the next column, format the cells where you’ll select the guest names. You’ll add data validation to those cells later.
Add a Formula to the Guest List
On the Lists worksheet, you’ll add a formula to check if the name in that row has been assigned a seat. The first name is in cell B1, so enter this formula in cell C1:
=IF(COUNTIF(TablePlan!$D$2:$D$25,B1),"",ROW())
Copy the formula down to the last name.
If a guest has been assigned to a seat, their name will appear in column D on the TablePlan sheet. In that case, this formula will return an empty string. If a seat hasn’t been assigned, the formula will return the row number.
Create the List of Unassigned Guests
In column D, you’ll add an array formula to list the unassigned names. This list will be used for the data validation drop down lists.
In this example, cells D1:D24 are selected, and this formula is array entered (press Ctrl+Shift+Enter)
On the TablePlan sheet, select the cells for Guest names, cells D2:D25 in this example.
On the Excel Ribbon, click the Data tab
Click Data Validation
In the Data Validation dialog box, from the Allow drop down, select List
In the Source box, type: =NameCheck
Click OK
The drop down lists are added to the cells, and you can select a guest name.
Link the Seats to the Guests
To show the guest names on the assigned seats, you can link the shapes to the cells.
Click on the shape for Seat 1 at Table 1.
Click in the Formula Bar
Type an equal sign, then click on the guest list, where the Table 1 Seat 1 name will be entered
Press Enter to complete the link
Repeat these steps to link all the seats to the guest link cells.
Assign the Guest Seats
Now you can select guest names from the drop down lists on the TablePlan sheet, and get your party organized. Keep all those arguing relatives at separate tables, and everything should go well.
Download the Sample File
If you’re planning your own party, you can download the sample file with an Excel seating arrangement example.
This week I’m working on a client’s sales plans for the upcoming fiscal year. They forecast sales per month by product and customer, and we use some pretty complicated formulas to sort things out. Of course, anywhere that it makes sense to use a pivot table, I create one. It’s a great way to summarize all the details, and review the overall totals.
For example, on a worksheet you can use formulas to create a running total, but in a pivot table it’s much easier — you can quickly create running totals with a couple of mouse clicks.
Let’s take a look at a pivot table based on some faked sales data. In the screen shot below, you can see the total sales per region per month, and the Grand Total per month. By changing the Sales field settings, you can show a running total, instead of the normal Sum.
Add the Running Total
To change the sales field, and show a running total, follow these steps:
In the pivot table, right-click one of the Sales amount cells.
In the context menu that appears, click Summarize Data By
Click More Options
In the Value Field Settings dialog box, click the Show Values As tab
From the Show Values As dropdown list, select Running Total In.
Select the Base Field where you want to see the running total. In this example, we’d like to see the running total down the list of dates, so OrderDate is selected as the Base Field.
Click OK, to close the Value Field Settings dialog box.
The pivot table changes, to show the running total for sales.
In the following screenshot, you can see the running totals in column C, and the original monthly totals in column H. Each month’s total sales is added to the previous total, to show the running total.
Change the Running Total Base Field
The most common use for running totals is to show amounts accumulated over time, as in the sales by month example above. However, you can use a non-date field as the base field for a running total. For example, in an election, you could show a running total of votes as each district submits its results. Or, for a large construction project, you could show a running total of expenses over the project phases.
In this pivot table, I’ve added City to the Column area, and used that as the Base Field for the running total.
Now, instead of the running total going down the pivot table by month, it goes across the pivot table, by city.
Be careful though — if you use a Base Field that isn’t in the pivot table layout, you’ll see #N/A for all the running total values.
Running Totals in Excel 2003 Pivot Tables
The running total technique is similar in Excel 2003 pivot table, and you can see the instructions here: Excel 2003 Pivot Table Running Totals. It also shows the results when there are multiple fields in the row area, and a running total is added to one of those fields.
Watch the Running Totals Video
To see the steps for creating running totals in Excel 2003, please watch this short Pivot Table Running Totals video.
A very nice email, from someone who visited the Contextures website, made me think about how long I’ve been using Excel. My guess was that I’d started around 1987, so I fired up the old Mac laptop, and dug some old floppies out of the storage cupboard.
The laptop is a Mac PowerBook 170, from late 1991, and it’s running the Mac version of Excel 3.0. There’s a Mac 128K model in the back room too, but I didn’t have the strength to dig that out. (I hope the producers of the tv series, The Hoarders, don’t call me now.)
As you can see, the application files were much smaller in Excel 3.0. Of course, that still took a good chunk out of my 40MB hard drive.
Flashy Excel Charts
Even though the machine didn’t have colour, I was still able to make some pretty flashy 3-D charts in the old days. I have no idea what this was supposed to show, but maybe it was the results of an Olympic ski jumping event.
I’m sure that I never wasted any time playing TETRIS, when I was supposed to be working on Excel files.
Sorting a List
Way back then (and until Excel 2007), we were only able to sort by 3 levels, and we couldn’t sort by colour. That didn’t matter much to me, since I didn’t have colour!
My Oldest Excel File
There may be something older on a floppy disk at the back of the storage cupboard, but I finally found an Excel file that I’d worked on in April 1987. I was creating some Excel training files, to be used by Apple vendors.
The Excel 3.0 File and Excel Toolbar
Here’s what the file looked like, and it’s interesting to see the minimalist toolbar too. Do you remember what all those icons were for?
How Long Have You Used Excel?
There’s an Excel poll on my Debra D blog, so if you have a minute, please go and answer the question – How Long Have You Been Using Excel?
It’s About Time
When I was thinking of a title for this blog post, a really old (and really bad) tv series popped into my head – It’s About Time. The show was about two astronauts who accidentally break the time barrier, and go back to prehistoric days, where they live with a cave family. In a strange coincidence, Wikipedia says that one of the astronauts in It’s About Time was named Mac. By the way, they changed Imogene Coca’s character name to Shadd, after the pilot episode.
The series was by the creator of Gilligan’s Island, and used sets, props and music from that series. Here’s a clip from YouTube that shows the opening and closing credits and theme song. Please don’t blame me if the song gets stuck in your head.
You can use Excel VBA to show messages to someone who’s using your workbook. This messages lets the user know that a customer name must be selected, and there’s an OK button to click after reading the message. (No one would ever click the OK button without reading the message, would they?)
But sometimes you want to get information from a user, instead of giving it. Let’s look at a couple of ways to do that, in Excel VBA.
Add Buttons to an Excel Message Box
The message box that’s shown above appears if someone tries to print an order form, and a customer name hasn’t been selected. Here’s the code that we created in the previous blog post on using Excel VBA to show messages.
A message box can return information to Excel VBA, as well as give information to the user. Instead of a single OK button on the message box, you could show Yes and No buttons. Perhaps the user needs to print an order form occasionally, without a customer name.
We’ll change the message text, to ask, “Print without Customer Name?” and we’ll change the message box style from vbCritical to vbYesNo.
Now, if you try to print with no customer name, the message box shows the Yes and No buttons, with the new text.
Change the Button Behaviour
With the new message box code, we have two buttons, but it doesn’t matter which one you click – the printing is still cancelled. We’d like to change the code so that if you click No, the printing is cancelled, and if you click Yes, the order form is printed.
When someone clicks a button in a message box, it returns a numeric value. There’s a list in the Excel VBA Help that shows the value returned by each button.
So, if someone clicks the Yes button, it returns a numeric value of 6. We’ll change the code to capture this.
We’ll add a variable, lRsp, to the code, to store the returned value.
At the start of the MsgBox line, add the lRsp variable, and enclose the MsgBox arguments in brackets.
Add an If…End If statement, to cancel the printing if the returned value is not equal to 6.
Download the Sample File
To see the order form and the completed Excel VBA code, you can download the Excel order form. Enable macros when the file opens.
Use an Excel UserForm
If you need to collect more than a Yes or No response from your users, you can use an Excel UserForm. There are written instructions and video tutorials here: Excel UserForm With ComboBoxes
In the old days, the Sort dialog box in Excel only had 3 levels. However, with a bit of planning, you could sort Excel data by 4 columns or more, and once you learned that trick, life was good. Or at least it was sort of good.
In Excel 2007, the Sort dialog box is much fancier, and you can include up to 64 sorting levels. I’ve never needed anywhere near that many – 5 or 6 fields is plenty for most tables that I’ve had to sort.
Sort By Colour
Another new feature in Excel 2007 is the ability to sort by cell or font colour, or by cell icon.
If you have different colours in a column, you can choose one to show up at the top or bottom of a sorted list.
If you used conditional formatting to add cell icons, such as traffic lights, you can sort by those icons.
To put the colours or cell icons in a specific order, you can add the same field multiple times in the Sort dialog box, and choose a different colour or cell icon for each sorting level. This won’t be too difficult if you have only a few colours in the list, but will be more challenging if you have lots of colours.
The list on my worksheet, that was previously sorted by date, is now sorted by the colours, in the order that I selected above. Yellow isn’t in the Sort level specifications, so it appears at the bottom of the list.
Sort By Colour At Your Own Risk
Even though you can sort by colour now, I wouldn’t recommend it. I’ve seen too many rainbow coloured Excel worksheets, where nobody can remember what the colours mean. Is yellow good? Is blue bad? Is there a colour code somewhere?
I’d rather add another column in the worksheet, and put a number code or text comment there. So, instead of marking the overdue accounts with a red fill colour, type “Overdue” in another column, or use a formula to calculate which accounts are overdue.
Or, instead of highlighting the customer names that you want to send an email to, type an X in an Email column. Then, you can sort or filter the Overdue accounts or the Email column, to focus on the rows of interest.
Do You Sort By Colour?
Maybe I’m missing something, and the feature is working well for you. Some people must have asked for the feature, since they added it to Excel 2007.
In your Excel worksheets, do you ever sort by colour? In what kind of lists or situations is it most helpful for you?
You might not love the Ribbon in Excel 2007, but the user interface does have new features that are an improvement over Excel 2003. The new features are useful when you’re working with large formulas or long names.
Change the Formula Bar Height
In Excel 2003, if you click on a cell that contains a long formula, it can spill onto the worksheet, hiding the column headings. It’s nice to see the formula, if you want to edit it, but annoying if you’re trying to do something else.
In Excel 2007, you can adjust the height of the formula bar, and the long formulas don’t cover the worksheet. To adjust the height:
Point to the bar at the bottom of the formula bar.
When the pointer changes to a two headed arrow, drag up or down, to change the formula bar height
After you’ve adjusted the height, you can quickly restore it to its previous height, by clicking the Collapse Formula Bar button.
When the formula is collapsed, the button changes to Expand Formula Bar. Click that to return to the previous height setting.
Also, if the formula is too long to show in the formula bar at its current height, scroll buttons appear, as you can see in the screen shot above. You can click the scroll buttons to view the formula, if you don’t want to change the formula bar height.
Widen the Name Box
Another new feature that I find really helpful is the ability to widen the Name Box. Instead of just seeing the start of a long name, and three dots, you can widen the Name Box to see the entire name.
To adjust the Name Box width, point to the dividing line at the right edge of the Name Box, and drag to the right.
To return to the default Name Box width, double-click the dividing line. (At least I think it’s the default width – I’ve changed my settings too many times to remember exactly.)
Excel’s Help says, “The maximum width of the name box is half of the width of the worksheet.” However, I’m able to widen the Name Box to the full width of the worksheet, and narrow it to nothing. Maybe that restriction applied in the Beta version, and the Help wasn’t changed.
Do You Use These Features?
I’ve used Excel for so many years without these features, that I often forget they’re available. Long formulas can be hidden in the formula bar when condensed, because they don’t spill onto the worksheet, the way they used to. If a formula is cut off in a logical place, you might not even realize that part of it is hidden. Those scroll buttons are very subtle, and are the only clue that a formula doesn’t fit.
Do you remember to use the new sizing features? Do you prefer them to the Excel static settings?
Watch the Video
Here’s a short video that shows the new features for adjusting the formula bar height and the Name Box width. It also shows how to temporarily collapse the Ribbon, so only the tabs are visible. That gives you an extra inch of space, if you need it when working on a large worksheet.
will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
An Excel workbook certainly isn’t Fort Knox, and the information you store there isn’t too secure. If someone opens your Excel workbook, and is determined to see everything in there, they’ll probably be able to. 
