the MOST

McCombs Office Solutions and Tips

Archives for Excel

Find Circular References in Excel

No circular references (when a formula or function references itsself) was one of the essentials in my Five Formulas and Functions Essentials post. Generally, if you have a circular reference, you’ll get an error message warning you about it. But the message doesn’t tell you where your circular reference is. And if you click Cancel on the message, it leaves the circular reference in the spreadsheet. So the question becomes, how do you find a circular reference in Excel?

 

Find a Circular Reference in Excel

Excel has built in error checking tools that will help you locate circular references.

  1. Go to the Formulas tab.
  2. In the Formula Auditing section, click the drop down arrow to the right of the Error Checking button.
  3. In the menu that drops down, hover over Circular References. A window will pop out to the side telling you which cells contain circular references.

 

Need to Use a Circular Reference?

In some cases, you may need to use a circular reference. If you do, Chandoo.org (a great Excel resource) has a really useful post all about how to use circular references. Read Chandoo’s post on circular references.

 

Sorting Horizontally in Excel

Screenshot of sales data in Excel

Have you ever needed to sort your Excel column headings?  In the data set above, I have sales figures for four products, each listed in no particular order.  What if I wanted to see those columns sorted alphabetically, from Apples to Zooms.  To do that:

1. Select the column headings you wish to sort and the complete data below them.  Do not select any columns on the left or right that you do not want to be sorted.  In the screenshot below, columns A and F are not selected, as I want to keep in place the Month and Total columns.

Screenshot of Excel data, with columns B, C, D, and E selected

 

2. On the Home tab, click on the Sort & Filter button.  From the menu, choose Custom Sort… 

Excel's Sort & Filter button with the Custom Sort option chosen

3. In the Sort window, click on the Options… button.

4. In the Sort Options window, click the option next to Sort left to right.  Then click OK.

Screenshot of Excel's Sort window

 

5. Back in the Sort window, click on the drop-down box next to Sort by, and select the appropriate row that contains your headings.  In this example, Row 1 is selected because that is where the column headings are located.

Screenshot of Excel's Sort option with Row 1 selected in the Sort by drop-down box

 

6. Make sure that the Order option is correct.  The default given is A to Z.  However, if you want your headings sorted in reverse order, choose Z to A from the drop-down menu.

7. Press OK.

Now, the headings have been sorted alphabetically, and the data underneath has moved with the column headings.  Both the Month and Total columns remain unmoved.

Screenshot of data in Excel, with column headers sorted alphebetically

 

Related Posts

1. Sort by Last Name When the Cell Contains the Full Name

2. Sort it Out in Excel

Sort by Last Name When the Cell Contains the Full Name

Generally, when someone asks how to sort by last name in a cell that contains both first and last name, I advise people to use the Text to Columns feature, but I’ve always wondered if there was another option. Today I found this video about how using the replace feature can isolate the last name in a separate column, making it easy to sort.

Before you watch the video, here are a couple of things to note:

1.  This video is in a pre-2007 version of Excel. The Sort and Replace tools in 2007 and 2010 are on the right side of the Home tab on the ribbon. Everything else works the same.

2.  This method assumes that you do not have any Jr’s or III’s or PhD’s tacked on to the end of your names. If you might, you should scan the Sort Name column afterward to check for these and correct them as needed, or do a Replace for the common ones and replace them with nothing.

Here’s a link to the post and video:

Sort by last name in a column that contains the full name.

The F8 Shortcut in Excel

Keyboard shortcuts are a fantastic way to save time. But when you accidentally hit a keyboard shortcut you didn’t know existed, they can be infuriating.

More than once, I’ve run into someone for whom Excel seemed to be going haywire. Any time they used the arrow keys in Excel, cells would be selected. Any time they clicked on a worksheet with the mouse, all the cells between the active cell and the cell they clicked on would be selected.

To be honest, this baffled me at first, and I usually suggested a reboot to solve the problem. But it turns out, this is called Extend Mode, and the F8 key turns it on and off, allowing you to use only a single mouse click or your arrow keys to select cells.

So if you’re experiencing the same problem, just press F8 on your keyboard to turn off Extend Mode.

F8 Keyboard Shortcuts in Excel

  • F8 – select cells with only a single mouse click or only the arrow keys
  • Shift + F8 – add a non-adjacent cell to your selection
  • Alt + F8 – opens the Macro dialog box

Change Worksheet Tab Size in Excel

Sometimes I feel like the text on the worksheet tabs at the bottom of Excel is on the small side. Today I picked up a great tip from OfficeUsers.org, that tells you how you can increase the size of these tabs by increasing the size of your side and bottom scrollbars.

When you increase the scrollbar size, the size increases not just in Excel, but in every program you use. If you try increasing the size and don’t like it, don’t worry, you can always follow the same process and decrease the scrollbar size (17 is the default).

Increase Worksheet Tab Size by Increasing Scroll Bar Size

Windows 7

  1. Right click on your desktop and select Personalize.
  2. Click on Window Color (at the bottom of the screen).
  3. Click Advanced appearance settings.
  4. In the Item drop down box, choose Scrollbar.
  5. Set the Size box to a larger number. (I found 22 to be a nice size.)
  6. Click OK.

Windows XP

  1. Go to the Start Menu and select Control Panel.
  2. Double click Display and go to the Appearance tab.
  3. Click the Advanced button.
  4. In the Item drop down box, choose Scrollbar.
  5. Set the Size box to a larger number. (I found 22 to be a nice size.)
  6. Click OK.

Windows Vista

  1. Right click on your desktop and select Personalize.
  2. Click on Window Color and click Advanced appearance settings.
  3. In the Item drop down box, choose Scrollbar.
  4. Set the Size box to a larger number. (I found 22 to be a nice size.)
  5. Click OK.
ChatClick here to chat!+