Hiding and Unhiding Using the Grouping Feature

In last week’s blog, I mentioned there was a little plus sign above Column C in the sheet named “List” in the file that accompanied that blog, as shown here:
 Grouping-05-the plus sign

Please click this link to open the Excel 2007 file (zipped, no macros) named “Vacation Packing List.xlsx” that accompanied last week’s blog. Notice also that Columns A and B are hidden. This was done by using a neat little feature called Grouping. I like this feature because it not only allows me to quickly hide and unhide columns and/or rows, but also allows me to clearly see where rows and/or columns are hidden. In this blog, we’ll cover how to use the Grouping and Ungrouping features.

First, let’s see how to use this Grouping feature, once it’s been established.

1. Click the little plus sign above Column C. Note that Columns A and B appear, as shown here:
 Grouping-10-expanded

2. Let’s click the minus sign that now appears above Column C. See how Columns A and B are hidden again?

Let’s see how to Group and Ungroup columns. Since we’re using a sheet that already uses the Grouping feature, we’ll use the Ungroup feature first.

1. Highlight Columns A and B.

2. In the Data tab, in the Outline group, click the Ungroup icon, as shown here:
 Grouping-15b-3-Ungrouping 

3. Note that the plus sign has now disappeared, as shown here:
 Grouping-20-After Ungrouping

4. Let’s Group Columns A, B and F. We’ll start with Columns A and B, since they are already highlighted.

5. In the Data tab, in the Outline group, click the Group icon. Note that the plus sign has now reappeared above Column C.

6. Let’s click Column F.

7. In the Data tab, in the Outline group, click the Group icon again. Note that another plus sign has appeared above Column G.

8. The top portion of the sheet should now look as shown here:
Grouping-25-1-Grouping

Finally, let’s see how to hide all of the grouped columns (works for rows as well) in one step. On the left side of the top of the sheet, click the little number 1 as pointed out in the screen shot just above. Did you notice that all of the grouped columns became hidden? Now, click the little number 2 just below the little number 1 we just clicked. See how the columns reappeared?

Next week, I’ll start a five week series on copying formats – Cells & Columns, Charts, and Printing. Hope you all have a good week.

Posted in Excel Grouping | Leave a comment

Packing for Vacation Using PivotTables

While PivotTables can certainly be used to summarize numerical information, they can also be used to organize non-numerical information. Case in point: packing for vacation. I used to have a vacation packing list in Word, but I found I needed a more flexible method because I wanted to have a packing list for the beach that would work whether I traveled by air or chose to drive and stay at a campground. There were many similar items I would need no matter how I traveled, so I decided to convert my list from Word to Excel.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Vacation Packing List.xlsx.” In this file, I started by creating a sheet named “List” and in a single column listed all the items I would pack if I traveled by air, and all the items I would pack if I traveled by car to a campground. I then added columns for category (Bath, Beach, etc), as well as two columns to show which bag I would use for the type of travel I planned. Next, I set up two sheets, each containing two PivotTables. One sheet, named “Pivot-by Category, by Bag” is what I plan to use as I gather items together to be packed.. The second sheet, named “Pivot-Final Check” is what I plan to use to make sure I actually packed everything. More information about how to create PivotTables can be found in Chapter 7 of the book “Simply Learning Excel 2007.”

How do you organize your vacation packing list(s)?

By the way, did you notice the little plus sign above Column C in the sheet named “List”? That’s another way to hide columns using a feature called Grouping, and I’ll discuss that next week.

Posted in Excel PivotTables | 1 Comment

“Simple” Giveaway Winners

Thank you all for participating in the “Simple” Giveaway. The winner of the top prize, with a value of approximately $145, is Chip. Chip will receive a copy of the book “Simply Learning Excel 2007” by Robert Ryan, MBA, CPA, and a live, personal, one-hour Excel session with Bob via WebEx. Bob will cover and/or answer any questions about any topic from the book, e.g. PivotTables, Filters, VLOOKUP, etc. This prize is transferrable, so Chip can give it to a family member or co-worker who needs some extra help, or they could even share it (the live session part) with up to 20 co-workers. Bob is looking forward to the session.

The four runners-up were Michael Tisler, Kanti, Gerry Bakker and Scott. Each of them will receive a free book, with a value of approximately $20 each. They, along with any reader of the book, can get their questions answered at no extra cost via a live WebEx session by submitting their questions to Ask the Author… LIVE!® at SimplyLearningExcel.com. The winners have already received an email letting them know they won.

Not surprisingly, the most common Excel feature learned and shared was PivotTables! Click this link to open an Excel 2007 file (zipped, no macros) named “Just the answers.xlsx” that shows a summary of all the answers. Click this link to download a video showing how the winners were determined.

Thanks again for participating, and we hope you visit again soon.

 Video showing winner calculation

Posted in Excel Giveaway | Leave a comment

Introducing “ex” Files; Ensuring Accurate Results with SUM

When I learn an Excel feature that I think is powerful but I’m not confident I’ll remember how to use it, or I find a way to help people learn a particular Excel feature or concept and want to remember it, I make up a simple example, and save it in an “ex” file. “Ex” is short for “Example,” and I have a bunch of them. Today, we’re going to talk about a more error free way of using the SUM function, although the solution can be applied to a number of other functions, and we’re going to use an “ex” file to demonstrate both the problem and solution.

When we click the AutoSum icon beneath a group of numbers that has a heading, Excel automatically defines the cells to be included in that function. But what happens if a cell(s) is inserted just beneath the heading, and/or above the SUM function but not immediately below the list of numbers? Unfortunately, the SUM function will not automatically expand to include the new numbers.

The fix? Include the heading in the SUM function to make sure that any rows inserted just beneath the heading are included, and add a row just above the SUM function labeled “Insert above this row.” If the file is to be presented formally, the row labeled “Insert above this row” can either be hidden or made extremely small. Click this link to open an Excel 2007 file (zipped, no macros) named “ex SUM More Error Free.xlsx” to see an example of the problem as well as a suggested solution.

Posted in Excel SUM function | Leave a comment

A “Simple” Giveaway

Do you consider yourself, or someone you know, somewhere between being a beginner to moderately experienced user in Excel 2007, and would like to know more? If yes, this “Simple” giveaway may be for you!

The Prizes

The top prize, with a value of approximately $145, is a copy of the book “Simply Learning Excel 2007” by Robert Ryan, MBA, CPA, and a live, personal, one-hour Excel session with Bob via WebEx. He will cover and/or answer any questions about any topic from the book, e.g. PivotTables, Filters, VLOOKUP, etc. Bob will contact the winner to arrange a mutually convenient time, and also find out what the winner would like to cover. This prize is transferrable, so the winner can give it to a family member or co-worker who needs some extra help, or they could even share it (the live session part) with up to 20 co-workers.

There will be four runners-up, and each one will win a free book, with a value of approximately $20 each. They, along with any reader of the book, can get their questions answered at no extra cost via a live WebEx session by submitting their questions to Ask the Author… LIVE!™ at SimplyLearningExcel.com.

How to Enter

Simply submit your answer to the following question by responding to this blog below:

  • In all the years (or days) that you have known Excel (any version), what is the most valuable thing – feature, formula, etc – you have learned about Excel,

 and/or

  • What is the one thing about Excel that you have shared with others that they have valued most? Was it PivotTables? Keyboard shortcuts? Spreadsheet design? Macros? Please let us know.

The Giveaway Rules

  • All responses must be submitted to Bob’s blog at SimplyLearningExcel.com/blog.
  • The entry deadline is 12:00 noon (USA Eastern time zone) on Monday, August 23rd, 2010.
  • One entry per person – any additional entries will be deleted from the draw – with one exception. If you answer both parts of the question, you will be entered twice.
  • A random draw (using Excel) will select the winners from all valid entries.
  • The winner will be notified by email, so please provide a valid email address. This will not be publicly visible, and you will not be added to any mailing list.

The winner will be announced on Bob’s blog on Tuesday, August 24th. Good Luck!

(A special Thank You to Debra Dalgleish, Microsoft Excel MVP and author of perhaps the best Excel website on the Internet at Contextures.com for helping to publicize this giveaway. If you haven’t already checked out her awesome site, I recommend you do so soon.)

Posted in Excel Giveaway | 21 Comments

Things That Can Go Wrong with Freeze Panes… and How to Fix Them

In my book, I stick to solutions that work. But when I teach an Excel course, I take time to point out things that can go wrong. For example, has your screen ever had crosshairs in the middle of the worksheet? If you’re not sure what I’m talking about, please click this link to open an Excel 2007 file (zipped, no macros) named “Freeze-Panes-Problems-1.xlsx” to view the problem. Or, have you ever opened a spreadsheet and tried to move down the spreadsheet using either the scroll bar or keyboard down arrow but nothing seemed to happen? Please click this link to open an Excel 2007 file (zipped, no macros) named “Freeze-Panes-Problems-2.xlsx” to view this situation.

Both of these problems are a result of someone incorrectly applying the Freeze Panes feature. In the first case, the crosshairs in the middle are a result of someone applying the Freeze Panes feature while Cell A1 is the active cell. In the second case, this can happen if someone applies the Freeze Panes feature while the data is filtered.

To fix the problem in the first case, Unfreeze the Panes, click some cell other than Cell A1, and reapply the Freeze Panes feature. To fix the problem in the second case, Unfreeze the Panes, make sure the data is not filtered, and reapply the Freeze Panes feature.

For more information on the Freeze Panes feature, please see Chapter 2 of "Simply learning Excel 2007."

Posted in Excel Freeze Panes | 16 Comments

Tips for Designing Spreadsheets: Part 2

I mentioned in my last entry that I strive to use the KISS method – Keep It Short and Simple – whether I’m creating a single, short spreadsheet or designing a complex workbook containing a series of large, related spreadsheets.  Here’s the second of two recommendations I have for designing spreadsheets in Excel:

When recording amounts, I (generally) record those amounts in a single column.  For example, if I’m responsible for keeping track of the cash received for the “Fun Day”, I’ll design my spreadsheet with four columns: Activity, Start Time, Stop Time, and Amount.  By designing the spreadsheet in this way, I can use Excel’s powerful summarization and reporting features.

Click here to open an Excel 2007 file (zipped, no macros) that contains a design I do recommend. Click here to open an Excel 2007 file (zipped, no macros) that contains a design I do not recommend.

Posted in Excel Spreadsheet Design | Leave a comment

Tips for Designing Spreadsheets: Part 1

I strive to use the KISS method – Keep It Short and Simple – whether I’m creating a single, short spreadsheet or designing a complex workbook containing a series of large, related spreadsheets.  Here is the first of two recommendations I have for designing spreadsheets in Excel:

All the content on a single sheet should (generally) be closely related.  For example, if I’m responsible for a “Fun Day”, I might have one worksheet showing the names and contact information of the volunteers, and a second sheet to keep track of who is going to run each activity during the day.

Click here to open an Excel 2007 file (zipped, no macros) that contains a design I do recommend. Click here to open an Excel 2007 file (zipped, no macros) that contains a design I do  not recommend.

Posted in Excel Spreadsheet Design | Leave a comment