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

Cheat Sheet of Excel Shortcuts to Make you Faster

Posted on by Posted in Microsoft Excel

The table of shortcuts below will help you to more quickly and efficiently enter data, create names and complete functions once you start using them every day.

Inserting content into an Excel worksheet is something most of us do every day. We are constantly entering text or data into our worksheets. Some of the keyboard shortcuts below may be familiar to you, but I am guessing you will find some new ones you can use as well.

Shortcut
Description
Insert Data
CTRL + K Insert a Hyperlink
CTRL + ; Insert the current date
CTRL + : Insert the current time
CTRL + Enter Enter value into non-contiguous cells
Alt + Enter Wrap test to new line in same cell
Alt + Down Arrow Display AutoComplete list
Shift + F2 Insert comment
Insert Functions
Alt + = Insert sum function
Shift + F3 Launch function wizard
Ctrl + Shift + A Completes function by inserting argument
Insert Names
CTRL + Shift + F3 Create names from labels in adjacent row and/or columns
CTRL + F3 Launch Name Manager
F3 Displays Paste Name dialog

How to Move a Chart Legend in Excel

Posted on by Posted in Microsoft Excel

Want to move your chart legend to a different location on your chart? Easy to do!

Excel ships with a wide variety of charts you can use to represent your data graphically in your worksheets. These charts that are included though, are just the beginning! You can modify the included charts quite a lot, such as adding a legend. You can also move that legend to another position within your chart!

Follow the steps below to learn how:

  1. Simply click on your legend once to select it. Eight handles will appear around the perimeter so you will know it has been selected.
  2. Now click within your Legend and drag it to the desired position on your chart.
  3. Once you have released your mouse button the legend remains where you dropped it.

How to Link and Unlink Content Between Two Workbooks

Posted on by Posted in Microsoft Excel

Have you ever worked in one workbook and wished that you could refer to the contents located in an entirely different workbook? Guess what? You can! All you have to do is create a link between the workbooks.

Follow the steps below to learn how:

Version 2007:

  1. Have both workbooks open.
  2. Select the cell whose value you would like to link into the other workbook.
  3. Right-click the cell to display the shortcut menu and select Copy or simply use the CTRL + C shortcut.
  4. Now switch to the other workbook by selecting Switch Window and clicking on the name of the workbook you would like to view.
  5. Click where you want to paste the link.
  6. Click your Home tab and click the arrow on the Paste button to open the full emnu.
  7. Select Paste Special and the dialog box will display.
  8. Click the Paste Link button.

The link has now been pasted into the cell and you can see the link reference in your Formula bar.

To test your link, switch back to your other workbook.

  • Click the Operations tab and change one of the values on which the linked value depends.
  • Switch back to the workbook to see if the linked value changes.

If you would like to send a workbook to a colleague who does not have access to the linkied workbook, you will want to first unbreak the link or the person to whom you send your workbook will receive an error when he or she tries to open the workbook.

To Unlink follow these steps:

  1. Click the Office button and select Prepare and click Edit Links to Files so that the dialog box displays.
  2. Select the link to break if it is not already selected.
  3. Click the Break Link button.
  4. A confirmation dialog box will appear. To break the link, click the Break Links button.
  5. Click Close on the Edit Links dialog box.

To verify that the link has been broken, switch back to your other workbook and again change a value on which the copied formula depends.

Switch back to the other workbook.

The value should not have changed because the link between the two workbooks no longer exists. The cell now contains a fixed value rather than a formula.

In version 2010:

  1. Right-click where you would like to paste your link and select Paste Special in the menu that appears.
  2. Click the Paste Link button.
  3. The link is then pasted into the cell.
  4. You can see the link reference in your Formula bar.

To test the link, switch back to your other workbook.

Change one of the values on which the linked value depends..

Switch back to the first workbook to see if the linked value has changed.

To send a linked workbook to a colleague who does not have the source workbook, you will first want to unlink the workbooks or the person will receive an error when opening the linked workbook.

To Break the Link:

  • Select Edit Links to Files to open the dialog box.
  • Select the link to break if it is not already selected.
  • Click the Break Link button.

A confirmation dialog box will display. To break the link, click the Break Links button.

  • Click the Close button to close the Edit Links dialog box.

Notice that the cell now contains a fixed value rather than a formula.

To verify that the link has been broken, switch back to the other workbook.

Again, change a value on which the copied formula depends. Switch back to the first workbook.

The value has not changed because the link between the two workbooks no longer exists.

How to Insert a Watermark Behind Merged Cells in Excel

Posted on by Posted in Microsoft Excel

One of my readers, Edwin, asked me if it was possible to insert a watermark into a group of merged cells in his Excel worksheet. He actually wanted a comment in there but wanted it to be constant and stay behind any entered text.

Excel does not provide this sort of capability but there are some workarounds that I told Edwin about:

  • If he wanted his comment to be text rather than a graphic, he could use a text box. Simply create a text box that is the same size as your merged cells and position it over those cells.
  • Then add your comment to the text box.
  • Format the font as light gray for your text box.
  • Make sure your text box has no border and the background is transparent.

It is very important that your background is transparent since you cannot position the text box behind your worksheet. Your text box is on the drawing layer which is always over your worksheet. You can arrange objects on the drawing layer but you cannot more the drawing layer itself.

If folks use the tab key to move into the merged cells, they can type and it will appear in the cells. You might have to fiddle with colors and appearance because the gray text in your text box will not go away when you enter text in the underlying cells.

Please note, however, that if someone uses their mouse to click on the merged cells they will instead, select your text box. You can get around this obstacle by selecting your merged cells, making sure they are not protected and then protect your worksheet as a whole. By default, text boxes are protected so the cannot be selected in a protected worksheet. So now, if someone clicks on the merged cell, it is the cell that is selected, not your text box.

How to Clear Cells by Dragging in Excel

Posted on by Posted in Microsoft Excel

If you like to save time you will love this tip!

A reader wrote to me recently asking me for a quick method of clearing cells in her worksheet.

Follow along below to learn how:

  1. Select the cells you would like cleared.
  2. If you have your mouse in your hand, grab the fill handle at the bottom right corner of your selection and drag it back over the selection area.
  3. As you drag, you will see the cells are grayed out.
  4. When you release your mouse button, the values in the cells have been cleared.

If you want to clear the formatting in your cells as well, hold down your Ctrl key as you drag the fill handle back over your selection.

How to Record a Dynamic Macro in Excel

Posted on by Posted in Microsoft Excel

Want to create a dynamic Excel Macro that will work on variable-size arrays? You’re in luck!

Follow the steps below to learn how:

Place your cursor at the starting position of your raw data.

  1. Launch the Record Macro dialog.
  2. Assign a shortcut to your Macro so that you can easily repeat it.
  3. Click OK on the Record Macro dialog.
  4. Use relative references.
  5. Click on View | Macros | Macros | Use Relative References.
  6. Do this before you perform any movement of your cursor.
  7. Record one instance of the Macro regardless of how many blocks of data you have.
  8. Stop the Macro when your mouse is at the starting position of your next block of data.

You have now created a dynamic Macro.

Now here is how you use it:

Place your cursor at the starting position and then click the Macro shortcut you assigned to it. If you want to process N blocks of data, click CTRL + YourKey (x) number of times, or keep your shortcut pressed and the Macro will be repeated without effort.

How to Copy Cells to Fill a Range in Excel

Posted on by Posted in Microsoft Excel

Excel gives you a pair of shortcut keys that can be used to copy cells down and to the
right.

For example, say you want to copy the contents of cell B3 to the fifteen cells just
below it.

Follow the steps below to learn how:

  • Select the sixteen cells (making sure that cell B3 is the top cell in the selection) and then press Ctrl+D. Cell B3 is copied to all the other cells below it.
  • Alternatively, if you would like to copy cells to the right, you can make a selection and Click Ctrl+R. The value of the left-most cell in the selection will be copied to its right, filling out
    the selected range.

Locked File in Excel?

Posted on by Posted in Microsoft Excel

On more than one occasion I have had a reader write to me perplexed as to why every time they would open a workbook a dialog box would appear that said the file was locked and they would have to save changes under another name. Even after doing that, the dialog sill appears on the new workbook!

Well the simple truth of it is that sometimes good old Excel becomes confused just like we do at times and thinks your file is locked by another user when it isn’t.

The first thing I advised was to restart their computer or copy the workbook to another folder on their system. Restarting or copying the workbook to another folder can sometimes help clear up Excel’s confusion.

More than likely though, the cause of the problem is that there is some sort of macro running when the workbook is opened. Even if you save the offending workbook under a different name, it still has the macro saved with it and the process will just repeat itself all over again. You can test to see if this is the problem.

Follow the steps below to learn how:

  1. Open the workbook as you normally would, dismissing the dialog box.
  2. Select all the worksheets in your workbook by clicking the left-most worksheet and holding down your Shift key as you click on the right- most worksheet.
  3. Select Move or Copy Sheet from the Edit menu. (To display the proper dialog box in version 2007, display the Home tab of your Ribbon).
  4. Click the format tool in the Cells group and then select Move or Copy Sheet from the resulting submenu

Move or Copy

  1. Using the To Book drop-down list, select new book.
  2. Be certain the Create a Copy check box is selected.
  3. Click OK.

Now you have two workbooks – the one you originally opened and the new, unnamed workbook.

Close the original workbook without saving and then save the new workbook under a new name. You should now be able to open the new workbook with no problems, as the macros are not copies when you follow the steps above.

Display Row and Column Labels in Excel

Posted on by Posted in Microsoft Excel

If you add column and row labels to your worksheet data, it just makes sense that you would want them to appear on screen at all times so it makes it easier to work with your data. It can be very tedious if you have to keep scrolling up and down to see what you are doing.

  • To keep your row and column labels visible, you can freeze the rows and columns where your labels are located. You could, for instance, easily freeze the first three rows of your worksheet along with the first column. Then, when you scroll the worksheet the rows and columns will remain on the screen. Only the unfrozen part of the screen will scroll.
  • You specify which rows and columns you want to freeze by selecting the cell immediately below and to the right of the area to be frozen.
  • Then, to freeze the rows and columns you would select Freeze Panes from the Windows menu. In version 2007 you would display the View tab of the Ribbon and click Freeze Panes in the Window group and then click Freeze Panes again.
  • MS Excel will place a thicker black line above and to the left of the current cell to indicate the rows and columns that are frozen in your worksheet.
  • If you should decide that you no longer need frozen panes, you can simply select Unfreeze Panes from the Window menu.
  • In version 2007, you display the View tab of the Ribbon and click Freeze Panes in the Window group and click Unfreeze Panes.

Obscure Printing Tips for Excel

Posted on by Posted in Microsoft Excel

When you print in Excel the pages normally print from top to bottom. Should the print area be too wide to print on one page, MS Excel will just print from the top down and then left to right.

Do you ever have worksheets that contain data that spans more than one page width? If so, you may prefer to print your report starting at the top left page, going right and then down.

Whether your worksheet prints Down and then over or Over and then Down is all in your control. You simply select your preferences in the Page Setup dialog box.

Now we get to the interesting stuff! Yes, stuff that most Excel users are not aware of.

If you select multiple non-contiguous ranges on the same worksheet for your print area, each area will print on a separate page. You can control the order in which these ranges are printed by simply selecting the ranges in the order in which you would like them to print before setting your Print Area. You are not restricted to Down, then over or Over, then down!

Follow the steps below to learn how:

  1. Select Range 4 first.
  2. Click on CTRL and select Range 1 second.
  3. Click on CTRL and select Range 3 third.
  4. Click on CTRL and select Range 2 fourth.
  5. Set the Print Area.

When you print your worksheet:

  • Range 4 will print on page 1.
  • Range 1 will print on page 2.
  • Range 3 will print on page 3.
  • Range 2 will print on page 4.

Pretty cool eh? I am sure you will find this helpful.