Subtotals are Cool!

Using Excel’s built-in Subtotal feature can save you an enormous amount of time and can add some nifty (does anyone still say, “nifty”?) extra organizing muscle with practically no effort.  Rather than manually inserting blank rows between your groups of data and inserting your Sum functions, you can create your Subtotals in less than 60 seconds.

  

Here is How You Do It:

1.  Choose a worksheet on which you want to try using Subtotals on, and save it as a practice file, (perhaps as “Subtotal Practice”).  This is important, since you don’t want to have the possibility of messing up your original data.

2.  Then Sort Your Data according to the column on which you wish to subtotal.

3.  Select any cell within your data and go to Data / Subtotal.  This will generate a dialogue box that will give you your choice of which Column you wish to base your subtotal on, the Function (several choices in addition to Sum), and the column of data you wish to actually subtotal (Excel will guess, but you may have to change it).

4. Click OK and Voila!  Subtotals!

But, Wait, That’s Not All!  Excel will also provide the additional functionality of being able to Roll-Up or Expand your view of your worksheet based on the Subtotal Groupings.  This is a nice feature when you want to present your findings in a clean, professional manner (and, of course, you always want to do that…).

                                                                                      

Try out this feature and find out why “Subtotals are Cool”!

Lists and Tables

A very handy, and often times overlooked, feature that was introduced in the Excel 2003 version is the List (and is subsequently called the Table in later versions).

I have long been a huge fan of using databases for keeping track of, and manipulating complex data. Sometimes, however, a user’s needs are much simpler. In situations where your data is more like a List or Table, the ability to quickly and easily format it for your needs is a real boon. Quite simply, it can save you time and effort!

For instance, let’s say you have small database in Excel 2007 that you wish to convert to a table. It could hardly be easier: Simply select one cell in the database and on the Home Tab go to Format as Table in the Styles grouping. Then choose the Table Style you find creates the Best Look for your table. Once you have clicked on that, you gain the following benefits:

1. Sorting Dropdowns with optional Text Filters are instantly added to your table
2. Your entire table is Formatted in a professional crowd-pleasing style that makes it easier to use.
3. You can Continue to Add Data in the same formatting by selecting the last row and tabbing to enter more rows and data
4. Tables and their categories can add simplification when used as References in other formulas
5. Combining these newly formed Tables with Pivot Tables can create a powerful system that requires barely any work at all!

Lists and Tables can quickly add a little Ease to your life in Excel (and who wouldn’t like a little more Ease in their life…).

Happy Excelling, All!

Double-Click Tricks

As a regular Excel user, you are probably familiar with several Double-Click shortcuts that save you time.  There are some Double-Click Tricks that may have escaped your notice, however, so this week we are going to look at 4 that are a bit less than common.  The first two are for Excel 2007/10 users, but the last two work for any version:

1.  Double-Click on the Office Button to Close Excel

Yeah, it’s just that simple.  It will, of course, display a warning if the workbook is not saved.

2.  Double-Click on Ribbon Menu Names to Collapse Ribbon and Get More Space

Bamm!  You can double-click on the menu names to shrink the ribbon to one line.   Want it restored? – Just double-click again and it all returns.

3.  Jump to Last Row / Column in Your Table with Double-Click

This is always a Crowd-Pleaser:  Simply select any cell in the table and double-click on the Cell-Border in the direction you want to go.  Presto – You will be transported to the last column or row in your table (Beam me up, Scotty…).

4.  Double-Click in the Corner, Just Above Scroll-Bar to Create a Split

Splitting your panes in Excel is highly useful when you want to see multiple areas at one time.  You can very quickly insert these Splits by clicking on either the Little Bar Shape next to Horizontal Scroll-Bar near bottom right corner of the Excel window or directly above the Vertical Scroll-Bar.  Then you can drag the bars wherever you wish.

Double-Click Tricks can save you time and make you Look Cool in the process!  (And who among doesn’t like to Look Cool?...)

Excel Turns 25!

Looking back on 2010, it is interesting to note that our baby, Excel turned 25 in September. Introduced as a new spreadsheet program in late 1985, and (interestingly) designed for the Apple Macintosh, the new product gave Apple a badly needed business application.

Even if you were an adult back in 1985, it can be hard to recall some of the big events, and how different things were back then. Back to the Future was the highest grossing film of the year. It was announced that Christie McAuliffe would become the first school teacher to ride aboard the ill-fated Challenger. 10,000 people were killed in an 8.1 earthquake in Mexico City. It was in 1985 that the CD-ROM was developed. 1 Mb of RAM (Imagine!) was the standard in PCs. Windows 1.0 was released, we were singing, “We are the World”, and 25 year-old Doug Klunder became the proud Father of Excel.

Nowadays, our daily lives are intertwined with Google, Facebook, and Twitter. In 1985, however, few people had a personal computer and, of course, the Internet was in its infancy. It was in these very different times that a Great Energy was released.

A case can certainly be made (and undoubtedly debated) that of all technological developments, Excel has had the biggest impact on the corporate environment and society in general. It has been used for such common applications as planning the household budget, and such near-devastating purposes of planning out ultra-complex securities that nearly took down our national economy.

Excel has indeed put great power in the hands of those who learn it and apply it with a strong sense of purpose. So, Happy Birthday, Excel! It is a New Year and we will most certainly find new ways to use your muscle.

Option Buttons

In the old Clint Eastwood movie, Magnum Force, Harry Callahan used the line, “A man’s got to know his limitations”.  That may be so, but it is always good to have Options.  For this last post of 2010, I thought it would be interesting to take a look at Option Buttons.

Option Buttons can be very useful in creating Quizzes or Polls in Excel, and are not too difficult to create.  There are a number of steps, but I guarantee that it will be well worth it if you hang in there and give them a try!  Here is how you do it:

1.  Make sure the Developer tab is visible on your toolbar

2.  Under the Developer tab, choose Insert / Option Button (Form Control)

3.  Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4.  Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box (Form Control)

5.  Then draw your Group Box all the way around your Option Buttons

Stick with me now, we’re almost there!

6.  Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7.  Now is where the Fun really begins.  Create a Formula that is based on the value that is shown in the Cell Link

8.  For example, let’s say you have linked three Option Buttons to cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF(E5=3, "Correct!", "Incorrect"))

Now when the user chooses Option 2 of the three possible, they are rewarded with the “Correct” feedback.  Option Buttons, give them a try; “It is always good to have options…”.

Happy New Year All!

            

A Little Excel Magic

Some shortcuts and tips can often appear Magical!  Here are a few of my current favorites:

Select Noncontiguous Cells

If you want to select noncontiguous cells in a worksheet, just hold down the CTRL key and click on the cells you wish to select.  Presto!

Enter Strings Starting with Zero

Sometimes, as in the case with zip codes, you want to enter strings of numbers that start with zero.  To do this, simply type an apostrophe first.  Alacazam!

Rearrange Rows and Columns

So, you have your data are in rows and you want them in columns (or vice versa)?  Highlight the cells that have your data, right-click, copy, and choose Paste Special. Then check the "Transpose" box. Voila!

A Room with a View

Feeling a bit cramped when viewing your spreadsheet?  You can hide columns or rows you don't need to be visible by right-clicking and selecting “Hide”.  Any functions tied to data in the hidden areas will still operate fine.  Open Sesame!

Instant Chart

Impress your friends and family by making an instant chart.  Simply select your data and click F11.  Magic!

I hope you have a Magical (and safe) Holiday Season!

Concatenation Revisited

This is one of my favorite time savers. I teach Excel, and I have had classes as large as 200 students. Since I believe strongly in frequent communication, I keep in touch with individual students and the group as a whole. Having a spreadsheet of student names (or company employees perhaps) is great to stay organized, but what if you want to use your data for sending emails? This is where Concatenation Shines!

Consider that you have a list of students with the First Names in Column A and Last Names in Column B. The good news is that you can easily combine them into an Email-Friendly column of names in a “Last Name, First Name” format is easy. The secret to Concatenation is using Ampersands and cell references combined with quoted text or punctuation. Here’s how to do it:

Assuming your table starts in cell A1, put the following formula in C1 (be sure to note that there is a space after the comma in quotation marks):

=B1&", "&A1

This simple formula combines the contents of B1 (last name)with a comma, space, and contents of A1 (first name). Select C1, place your cursor over the “handle” in the lower-right corner of the cell, and give a quick double-click. This will populate your Column C Email List down as far as you have data in Columns A and B.

Then it is a simple matter of copying the contents of Column C, and pasting into Outlook. Bamm! You have just saved a Ton of Time!