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

Carol's Corner Office

Category Archives: Excel 2010

Free Microsoft Excel 2010 articles

How to Use Numbers for Column Headings Rather than Letters in Excel 2010

Posted on by Posted in Excel 2010

By default, columns are referenced as letters in Excel. It doesn’t have to be that way though!

You can reference your columns by number rather than letter if you so choose.  It’s called an R1C1 style reference and it is more complicated, but it does serve a purpose and has often been needed for complex array formulas within macros in Excel.

Follow the steps below to learn how to change your column headings to numbers:

  1. Click the File tab of your Ribbon and Options.

Options

  1. Click on the Formulas tab.
  2. In the Working with Formulas section, select R1C1 reference style.

Formulas Tab

  1. Click OK and you are finished!

How to Turn Off Insert Options in Excel 2010

Posted on by Posted in Excel 2010

In Excel there is a feature that will  allow you to modify the way in which you insert rows, columns or cells in your worksheet. When you insert any of these, Excel will display a small, button near the inserted row, column, or cell.

The button,is called called Insert Options and it has a small paintbrush on it, similar to the Format Painter tool. Click the button, and Excel will display options regarding how the row, column, or cell you inserted should be formatted.

Some folks find the Insert Options button distracting, or they never use it. If you are one of those folk, you may want to turn it off.

Follow the steps below to learn how:

  1. Display the Excel Options dialog box:
  2. In Excel 2007 click the Office button and then click Excel Options.
  3. In Excel 2010 display the File tab of your Ribbon and then click Options.
  4. At the left of the dialog box click Advanced.
  5. Scroll through the available options until you see the section named Cut, Copy, and Paste.

Excel Options

  1. Deselect the Show Insert Options Buttons check box.
  2. Click OK.

How To Clear Cells Quickly and Easily in Excel

Posted on by Posted in Excel 2010

Everyone knows I love to save time and keystrokes and this is a tip that does both for you!

Follow the steps below to lean how

  1. Once you have a range of cells selected, just grab the Fill handle int he bottom-right corner of your selection and drag it back over your selection.
  2. As you drag, your cells become grayed out and once you release your mouse button, the cells will be cleared!

Want to clear the formatting in the cells too?

  • Simply depress and hold your CTRL key as you drag the Fill handle back over your selection

It just doesn’t get much easier than that!

How to Quickly and Easily Zoom in Excel

Posted on by Posted in Excel 2010

If you spend a lot of time zooming in your Excel worksheets I am sure you will agree that it becomes very tiresome very quickly! It also uses a lot of time which we all know I hate to do!

So, if you have a mouse with a small wheel between the left and right mouse buttons, there is a cool little trick that you can use to do your zooming.

All you really need to do is hold down your CTRL  key while simultaneously turning that small wheel!  Each click up or down will increase or decrease the zoom factor by 15%.

Also, you can use this method o zoom any magnification between ten and one hundred percent! If, for whatever reason you would want to, if you want to zoom over 100%, you would have to use the controls on the View tab of your Ribbon.

But, if you like this feature a lot and would like to use it to zoom all the time in your Excel worksheets, you can tell Excel to always use your mouse wheel to zoom.

Follow the steps below to learn how:

  1. Display the Excel Options dialog box (in Excel 2007, click the Office button and then click Excel Options). In Excel 2010, Click the File tab on your Ribbon and click Options.
  2. Click the Advanced option at the left-hand side of the dialog box.
  3. In the Editing area, select the Zoom On Roll with IntelliMouse check box.

Options

  1. Click OK.

You can now zoom using your mouse wheel or scroll through your worksheet by holding down the CTRL key while using the wheel.

 

How to Display a Hidden First Row in Excel

Posted on by Posted in Excel 2010

Excel is such a great program and it makes it so easy to hide and unhide rows in our worksheets, using the menus.

What isn‘t very easy is displaying a hidden row if that row happens to be the first visible row in your worksheet. (i.e., if you hide rows 1 through 5 of your worksheet, Excel will obey your instructions). If you decide later that you would like to unhide any of these rows, however, it is not very intuitive as to how you should go about that!

Follow the steps below to learn how to unhide the top rows of your worksheet when they are hidden:

  1. Click F5 to display the Go To dialog box.

Go To

  1. In the Reference field at the bottom of the dialog box, enter the number of the row range that you would like to unhide. (i.e., if you would like to unhide rows 3 through 4, key in 3:4). By the same token, if you would like to unhide row 1, you would key in 1:1.
  2. Click on OK. The rows you specified are now selected. You will not see it on your screen but they are.
  3. From the Format menu, select Row and then Unhide.

 

 

How to Protect your Worksheet Formatting

Posted on by Posted in Excel 2010

You can easily protect the formatting of your worksheet without it affecting your data or text.

Follow the steps below to learn how:

  1. Select all the cells in your worksheet.
  2. On the Home tab or your Ribbon in the Cells group, select Format Cells to open the dialog box.
  3. Make certain the Protection tab has been selected.
  4. Deselect the Locked check box.

Protection

  1. With your Home tab still displayed, in the Cells group, select Format | Protect Sheet, which will display the dialog box.
  2. Deselect the Format Cells check box.

Protect Sheet

  1. Click OK.

Not so difficult is it?

 

How to Sort or Filter Using Conditional Format Results in Excel

Posted on by Posted in Excel 2010

Supposing you had values in Column A of your worksheet and a list in Column B. There is a conditional format that make any value in Column A turn red if it is also in Column B and you want to sort or filter by that result.

This is not terribly difficult to do. All you have to do is set up your data and your conditional formats to your liking.

Follow the steps below to learn how to set up your sorting:

  1. Select all the cells you would like included in the sort.
  2. Display the Data tab of your Ribbon.
  3. Click the Sort tool to open the dialog box.

Sort

  1. Using the Sort By drop-down list, select Column A, where your conditional formatting has been applied.
  2. Using the Sort On drop-down list, select Font Color.
  3. Using the Order drop-down list under Order, select the color you would like to sort by. In this instance it would be red.
  4. Using the second drop-down list under Order, specify whether you want the red font cells to be on top or on the bottom of the list.
  5. Click OK.

That’s all there is to it. If the colors of your cells change because of conditional formatting, you can resort your table and you will have no problems.

Should you want to filter by the colors you can easily do that using an AutoFilter.

Follow the steps below to learn how:

  • Turn on AutoFiltering by clicking on the Data tab of your Ribbon.
  • In the Sort and Filter group, select Filter.
  • Click the down-arrow at the top of Column A and you will see many options available to you.
  • Select Filter by Color to display a list of the colors that are in your column.
  • Select the color that you would like displayed.  Usually one would select Automatic.

How to copy Filtered Data From A Table in Excel

Posted on by Posted in Excel 2010

Copying data from a table is fairly easy because you can do it using the copying method that you have been using for practically forever in Excel.

Follow the steps below to learn how:

  1. Filter your table as you normally do , so that it displays only the records you wish displayed.
  2. Select the table cells you would like to copy.
  3. Click CTRL + C to copy the data to your Clipboard.
  4. Select the cell where you would like to paste your copied cells.
  5. Click CTRL + V to paste the data.

When you use this method to copy data from a table, Excel will only copy those cells that are visible.

How to Get Help with Worksheet Functions in Excel!

Posted on by Posted in Excel 2010

There are times when you need a function in Excel and you know what it has to do with and perhaps part of the name, but just cannot remember the full function name!  I know as I age it happens to me more frequently than I care to admit!

If you, like I, have the same problem, follow the steps below to get some help with your Worksheet Functions:

  1. If the function has a lengthy name, key in what you remember and click the Tab key to AutoComplete the name for you.
  2. Once you have the function name, plus the first open parentheses, key in the part of the name you can recall and click CTRL + A to display a dialog box to help you complete the functions with explanations of each argument.

Function Arguments

  1. For more information on the function, click on the Help on this function link on the left-hand bottom corner of the Function Arguments dialog.

Adjust the Width of your Columns in Excel

Posted on by Posted in Excel 2010

Sometimes you inherit an Excel worksheet from one of your colleagues and discover that you cannot see all of the data that has moved outside the cell and is hidden.

  • In this instance, you can very quickly adjust the width of your column (columns) using your mouse.
  • Simply select the column and move your cursor to the edge of the column until the plus sign appears.
  • Drag to the right until you have reached the width you need.
  • If you would like to expand multiple columns, select a column, then drag to the right until all columns you need to adjust have been selected.
  • Now, go to the edge of the farthest column, until the arrow with the cross appears and expand to the right

These are great time savers!