Microsoft Excel – Carol's Corner Office (2025)

Named ranges can be great when you are writing formulas. Suppose if you assign the name WidgetPrice to cell A5, you can then use the name WidgetPrice in your formulas instead of A5. This will make your formula (and its purpose) easier to understand when you are working with them on a later date

This approach is great if you have not yet created any formulas. Suppose you already have a bunch of formulas in your worksheet, and they already reference cell A5 instead of WidgetPrice? You could, of course, select each formula and edit them to refer to WidgetPrice instead of A5, but that could certainly be a long process with a wide margin for errors.

The solution is to allow Excel to do the editing for you.

Follow the steps below to learn how:

1. 1. Define the named range you would like used in your worksheet.
   2. Select the cells that contain formulas.
   3. Display the Formulas tab of your Ribbon.
   4. Click the down-arrow at the right of the Define Name tool (in the Defined Names group) and then select Apply Names. Excel will display the Apply Names dialog box.

5. Click OK.

That’s all there is to it! Excel will examine your formulas and any reference to cell A5 is replaced with the name of A5, WidgetPrice.

You may not think so, but creating a Web page from one of your existing Excel workbooks is extremely easy.

Basically all you need do is drill down to the workbook you want to use and then click F12, which will open the Save As dialog box.

You must be sure that you change the Save As Type drop-down to Web Page.

You may have noticed if you were following these steps at home on your own computer, that there are some differences between this Save As dialog box and the one you would normally use for saving a workbook.

In this dialog box, you must indicate in the middle of the dialog box what you would like saved in your Web page. You can actually specify to save the entire workbook or you can save the worksheets you selected before you displayed the Save As dialog box.

If you chose to save only a worksheet, you can then specify that the output be interactive. Of course, this means that others who access the Web page can utilize the information in the same manner as if they had access to your Excel file, provided they have a version that is compatible on their own computer.

Also, you can specify a title page for your Web page. The page is displayed by a browser at the top of the page in the title bar. You can change the title by clicking your mouse on the Change title button.

Once you are ready, click on the Save button and Excel will create the HTML (hypertext mark up language) output file in the location of your choice. You can now place that created Web page on a Web server for folks to see.

Viewers will not need Excel. They will simply need a standard Web browser. Be aware that your page may be viewed differently on different browsers.

We all know that Excel workbooks can contain any number of worksheets and the tabs identifying those worksheets are located at the bottom of the screen.

I think most of my subscribers also know that when you have a workbook with many, many worksheets and all the tabs will not fit at the bottom of your screen, you use the navigation buttons in the bottom left-side of your screen to scroll through your worksheet tabs.

Still though, sometimes you have scroll a whole bunch because you don’t know whether you have to scroll right or left or how far, so it can be a pain.

I just learned this nifty little trick so I thought I would share it with you!

1. Simply right-click on the navigation buttons (just to the left side of your worksheet tabs).
2. Excel will display a Context menu that will list all your worksheets.
3. Simply select the the worksheet of your choosing and you are finished!

The first thing I am going to do is clarify that copying information between two instances of Excel is not the same as copying information between two worksheets opened in one instance of Excel.

Nowadays there are many offices with desks with two monitors on them. Amazing the advances we have made isn’t it? All this progress sometimes changes the way we work. For instance, you might open two instances of MS Excel and have one on each monitor so that you can work on both workbooks at the same time.

You will notice though, that copying information from one instance of Excel to another works quite differently than when you copy within the same instance of Excel. If you copy a cell that contains a formula and then paste it in the other instance, what gets pasted is the result of the formula, not the formula itself. And no, you cannot use Paste Special from the Edit menu either to paste formulas either when using two instances of Excel.

The bad news is that there simply is no easy way to get formulas from one instance of Excel to another. You can, however, use the workaround below:

1. In the source workbook, select a single cell that you would like to copy.
2. Click F2 to cause Excel to switch to Edit mode.
3. While still in Edit mode, select everything in the cell (entire formula).
4. Click Ctrl + C to copy the formula to the Clipboard.
5. Click ESC to exit Edit mode.
6. In the target workbook, select the cell where you would like to paste the formula you previously copied.
7. Click F2 and Excel will again switch to Edit mode.
8. Click Ctrl + V to paste the formula into the cell.
9. Click Enter.

Edit mode has now been closed and the formula is safely in your target cell.

Unfortunately this tedious procedure must be replicated for each formula you would like to copy between instances so most folks will forgo using separate instances of Excel and instead opt to work with different windows within the the same instance of Excel.

Follow the steps below to learn how:

1. Open the two workbooks with which you would like to work.
2. From the Window menu, select Arrange. In versions 2007-2010, click on the View tab of your Ribbon and click the Arrange All tool in the Window group.
3. Select Tiled or whichever option you prefer.
4. Click OK.

Your two workbooks are now side-by-side in your window.

5. Be certain the Excel window is not maximized.
6. Position the non-maximized window in the left-most of your monitors.
7. Drag the right border of the Excel program window onto your second monitor. A single instance of Excel now covers both of your monitors.
8. Within the Excel window, use your mouse to arrange the two workbooks so that one is on each monitor.

Since you are using a single instance of Excel (even though it extends across two monitors), you can now cut, copy and paste as you would normally.

You can use the ampersand operator (&) in MS Excel when you need to join text. In Excel the ampersand operator is known as the concatenation operator.

For instance, the formula =A2&B2 will join the text values shown in the two cells A2 and B2 below.

When using the ampersand operation you may want to include a space between the two items that are combined to improve the appearance. For instance, if the cells contain first name and last name, you may want to have a space between those names.

To include a space between cells, you will follow the & with a space enclosed in quote, such a &” ” shown below.

Martin, who works in the HR Department of his company, wrote to me recently with a query. It seems he had a list of ages in column A of his worksheet. The ages were from 1 to 100. In column B he had the number of folks of each age.

His query was – can you give me a formula that will result in the median age for this list of people?

Your first impulse may be to figure out the average age, but there is a difference between the average age and the median age. If you were looking for the average age, you could calculate that fairly easily by multiplying the age by the number of people of that age. In column C, you could key in a formula such as =A1*B1 and then copy it down the column, add up the values in columns B and C and divide the sum in column C by the sum in column B, which would give you the average age for the list.

The median age though, is the age where half the folks fall below that age and half above. The median age can best be calculated using an array formula such as the one below:

=MATCH (SUM ($B$1:$B$100) /2,SUMIF($A$1:$A$100,

“<= “&$A$1:$A$100,$B$1:$B$100))

This is a single formula, entered by clicking CTRL + Shift + Enter. The SUMIF function in the formula is used to generate an array of the cumulative number of people who are less than or equal to each age. The SUM portion of the formula gives the midpoint of the total frequency of ages. the MATCH function is then used to look up the midpoint value int he array of cumulative frequencies. This will yield an index number in the initial array and since the array is made up of the ages 1 through 100, the index number match is equal to the median age.

A reader wrote to me recently asking me how to use AutoFill in Excel to reflect every other day rather than consecutive days. Seems he was setting up a worksheet to keep track of his diabetes and wanted to use readings every other day.

I was happy to tell him that it was very easy to accomplish this task.

My Reply:

1. Simply key in October 6, 2011 in cell A1.
2. In cell A2, key in October 8, 2011.
3. Select both cells.
4. When you hover your cursor and see the Fill handle, drag down the column.

Voila! Every other day will appear in your cells.

Those of you like me, who love to use their keyboard to save time will love this information and those of you who didn’t know you could accomplish this task with your keyboard will be very happy to learn that you can!

Unfortunately, this is not terribly easy, but it can be accomplished. Follow the steps below to learn how:

Versions 2007 – 2010 (Still clunky) Row Height:

1. Click Alt which will make Excel display its “shortcut mode.” You should now see shortcut keys above each tab of your Ribbon.
2. Click H to indicate you would like to use the Home tab and new shortcut keys display above each tool in the tab.
3. Click O to indicate you want to use the Format tool in the Cells Group. Excel will display a drop-down list of options.
4. Click H to select the Row Height option and the Row Height dialog will display.
5. Key in the value you want for your row height.
6. Click Enter.

To specify row width, simply use the steps above and substitute W in step 4.

Another method that can be used in any version of Excel:

1. Click Shift + Spacebar to select the entire row.
2. Click Shift + F10 to display a Context menu.
3. Click R to indicate you want to change the row height.
4. Key in the value you want for your row height.
5. Click Enter.

For Column width:

1. Click Ctrl + Spacebar.
2. Click Shift + F1- to display a Context menu.
3. Click C two times to choose the second C command in the Context menu.
4. Click Enter and Excel displays the Column Width dialog.
5. Key in the value you want for your column width.
6. Click Enter.

Have you ever used Excel to record employee start and finish time? I know I tried it a long time ago and quickly became very frustrated because it would not display negative time. I tried everything and finally gave up in frustration.

Luckily I know some very smart folks who are much more proficient than I in MS Excel!

I learned the solution to this problem and now I will pass it along to you as well. So feel free to pass it along to anyone else who is frustrated!

The easiest method to resolve this issue is to change the date system used by your workbook. The default date system for for the Windows version of Excel is 1900 and if you use that date system, you will get ########## in your cell. If, however, you change to the 1904 date system which is the default for the Mac version of Excel, then you will see the correct negative time in your formula.

Follow the steps below to learn how:

1. Click Office Button | Excel Options to display the Dialog box.
2. Click Advanced.
3. Scroll through the options until you see When Calculating This Workbook.
4. Check the Use 1904 Date System.