Microsoft Office Tips, Tricks, and How-to's to help you use MS Office like a pro!

Carol's Corner Office

Category Archives: Microsoft Excel

Free articles about Microsoft Excel

Apply Auto-Fit to Multiple Columns in Excel At Once

Posted on by Posted in Microsoft Excel

I cannot tell you how many times I have been asked what this means in an Excel worksheet.

Pound signs in Excel

It is, of course, the result of values that are longer than the column in Excel can accommodate.

Perhaps you are aware that you can double-click the right border of the column’s header cell to automatically adjust the width of your column. Excel will increase the column width to accommodate the largest entry in your column.

What you may not be aware of, is that you can adjust the width of multiple columns at once.

Follow the steps below to find out how:

  1. Select the columns by dragging across the column headers.
  2. Hover your cursor over the right-border of any of the selected columns until the double-headed arrow appears.
  3. Double-click a column header border.

Do you know of any quick little tricks such as this one? If you do, please let me know so we can share your knowledge with everyone.

Bypass Menu & Ribbon to Add Columns & Rows in Excel

Posted on by Posted in Microsoft Excel

Inserting columns and rows in an Excel worksheet is an easy process. You can do this by simply selecting a column or row and clicking on Insert and the appropriate command.

  • In Word2007 and 2010 you can click the Home tab and in the Cells group, use the drop-down arrow next to Insert to select an item.

But who doesn’t want to save time and keystrokes? There are a couple of other methods to accomplish the same thing without using the menu and ribbon.

Follow the steps below to learn how:

  1. Right-click a selected column or row and select Insert from the resulting shortcut menu.
  2. MS Excel will insert a column, pushing everything to the right by one column.
  3. When inserting a new row, Excel will shift everything down by one row.

Now suppose you work in a very large firm and someone in the IT Department has disabled the shortcut menus.

There is still a way around it to save time. Use the Fill handle! I know, it can be a little tricky but you will quickly get the hang of it.

  1. First you select a column or row.
  2. Then depress your Shift key and drag the Fill handle one column to the right or left.
  3. If you want to insert a row, simply drag the Fill handle up or down.

When Excel displays a gray border around the adjacent column or row, just release the keys and a column or row will be inserted for you.

Unlike the other methods above, this method allows you to insert a column to the left or a row above the selection.

Edit Text Quickly in Multiple Excel Cells

Posted on by Posted in Microsoft Excel

One of my subscribers wrote to me with a problem. It seems he had a worksheet that had multiple cells containing different variations of what should be identical information.

Luckily, I was able to advise him of a trick he could use to quickly edit multiple cells and make them all the same at one time.

Suppose you had a book list to keep track of the books and their authors so that you would know what you had read when you visited the library or your local book store? You noticed one day that one of your favorite authors was listed with a middle initial, without a middle initial and with his name misspelled in various cells. So there is an example of different variations of names and misspellings.

To quickly correct the data in multiple cells, follow the steps below:

  1. Select all of the cells containing the name you would like to streamline.
  2. For non-contiguous cells, depress and hold down your CTRL key and click to select each cell.
  3. Now you can key in the correct name and click CTRL + Enter, which will enter the same value in all of the selected cells.
  4. If you would like to avoid keying in the name, with the cells selected, use your Tab key to move the active cell to a cell containing a correct name.
  5. Click the F2 key (which will put you in edit mode).
  6. Now click CTRL + Enter to copy the correct name into all of the selected cells.

If you have a very large list of cells that need to be corrected, follow these steps:

  1. Use AutoFilter to select the names you want to correct (multi-select is available in versions 2003 – 2010).
  2. Select all of the cells and click CTRL + ; (semicolon) to select visible cells only.
  3. Click the Tab key to move the active cell to a correct cell.
  4. Click the F2 key to enter Edit mode.
  5. Click CTRL + Enter to copy the value from the active cell to all the other selected visible cells.

Extra! You can also use this same F2 | CTRL + Enter trick when editing formulas.

How to Use Drag and Drop to Create Hyperlinks in Excel

Posted on by Posted in Microsoft Excel

Ever want or need to create a hyperlink from one workbook to another in MS Excel? There are many reasons why you may want to do this and it is a quick and easy way to link your data together. Fortunately there is a wonderful feature in Excel that allows you to accomplish this very easily.

Follow the steps below to learn how:

  1. Be certain that you have the two workbooks open – the one in which you would like the hyperlink to appear and the one that will be the target of the hyperlink. You should have both of them visible on your screen simultaneously.
  2. Select the range of cells in the target workbook that you would like to use as the target of your hyperlink.
  3. Move your cursor so that it is over the thick box surrounding your cell or range of cells. It will change into an arrow.
  4. Right-click and hold down your mouse button as you drag the selection to the cell in which your hyperlink will appear.

When you release your mouse button, MS Excel will display a Context menu.

  1. Select the Create Hyperlink Here option from the Context menu.

Excel will immediately create a hyperlink in that cell.

You must pay strict attention to the very first step above. The drag and drop method will only work if you are using two instances of Excel open at the same time. You cannot simply display two workbooks using Arrange from the Window menu. You must open Excel to open one workbook and then open Excel a second time with the second Workbook.

The Extend Selection function in Excel Cells

Posted on by Posted in Microsoft Excel

Everyone who used MS Excel will experience this mystery at some point. Perhaps you have already experienced it and it just about drove you around the bend trying to figure out what the problem was and how to resolve it.

Suddenly when you select a cell or use your arrow keys to move around in your worksheet, a whole range of cells is selected. No matter what you do, it will not allow you to select just one cell. It can be maddening can’t it?

The first time I experienced this was a very long time ago, but I was very perplexed all the same. It occurred more than once before I finally figured out the mystery.

  • What causes the problem is that you have somehow accidentally clicked the F8 (Extend Selection) key. You can tell that this is your problem by looking down at your status bar. If you see the EXT displayed at the bottom right-hand side of your screen you know you have turned on the Extend Selection feature.
  • The resolution is to simply click the F8 key again.

If you have not experienced this in the past, try it now just so you can become familiar with it. I find that if I do something once or twice it stays with me longer and you want to remember this one because sooner or later it will happen to YOU.

How to Copy Excel Page Setup from One Sheet to Another

Posted on by Posted in Microsoft Excel

Most times, print settings (i.e.), orientation, margins, headers, etc. are set for a specific worksheet in your MS Excel Workbook. When you add a new worksheet, MS Excel assigns the default page setup settings. If all of the worksheets in your workbook require the same settings, you can certainly adjust the defaults. It is very common, however, to have worksheets with different page setup settings in the very same workbook.

You could, of course, manually set all those options, but as you know around these parts, we like to save time and keystrokes! So, let’s save some time by transferring the settings from one worksheet to another.

Follow the steps below to learn how:

  1. Select the existing worksheet that contains the settings you would like to transfer.
  2. Select the target sheet or sheets, which are the ones you would like to update with those settings above, by clicking on CTRL + each sheet tab.
  3. Click on File | Page Setup.
  4. Click OK.

Two caveats

Make certain that you select the worksheets in the proper order, source first, then target. If you activate a target worksheet first, you will overwrite the settings you originally wanted to copy.

Also, be sure to ungroup your worksheets when you are finished. While grouped, MS Excel will execute any action you take on all the worksheets in the group.

Emphasize Important Values in your Excel Charts

Posted on by Posted in Microsoft Excel

When you are attempting to explain a theory or get your point across to others during a conversation, you normally will change the tone of your voice or the expression on your face.

When you have created a chart in Excel it is not always so easy to stress that important value that you want to stand out to your viewers. This tip will help make this task easier for you.

Follow the steps below to learn how:

You will most likely want to de-emphasize everything in your chart except the value that you want to stress and you can do this by:

  1. Right-clicking any bar in the series of your chart to select the entire series.
  2. Select Format Data Series from the resulting dialog box.
  3. On the Patterns tab, in the Area section, select a color such as light gray from the palette.
  4. Click OK.

Should your chart’s plot also happen to be light gray, which is the default in Excel, you will want to change that as well.

  • Right-click the background and select Format Plot Area from the resulting dialog box.
  • Select White from the palette.
  • Click on OK.

You are now ready to emphasize a single value in your chart.

  1. Click the item you would like to emphasize which will select the entire series.
  2. Click the item one more time to select just that value.
  3. Right-click the item and select Format Data Point.
  4. On the Patterns tab, select an appropriate color to make it stand out.
  5. Click on OK.

The value in your chart will now have some zing to make it stand out to the viewer.

View Formulas and Results Simultaniously in Excel

Posted on by Posted in Microsoft Excel

Viewing the formulas in your worksheet is a great technique to use when reviewing your spreadsheet for inconsistent formulas. In case you have forgotten, the method for doing this is clicking CTRL + ` (grave, which is located on the left-hand side of your keyboard beneath the tilde).

It sure would be handy if you could view your formulas and the results at the same time wouldn’t it? Well, as I have recently learned, it is not impossible to accomplish.

Follow the steps below to learn how:

  1. Click anywhere in the worksheet you want to view and click on CTRL + Home to go to cell A1.
  2. In version 2007, in the Window Group, click New Window from the View menu.
  3. Click in the Window group and click View Side by Side on the View tab.

You should now have two identical copies of your worksheet in view.

  1. In one of the windows, click CTRL + ` to display the formulas.

You can now simultaneously scroll through your worksheet and see the formulas in one window and their values in the other.

The reason for the first step, going to cell A1, is that when you open a New Window, MS Excel automatically scrolls the window to the top left corner and makes A1 the active cell. If the original window did not have A1 as the active cell, your rows and/or columns would not be in sync for scrolling purposes.

Please note that when you open a New Window, any split or frozen panes and custom Zoom settings will be removed. To be certain you do not lose these settings, close the new window first. You can identify the new window by the :2 after the file name in the window header.