CIO-Information Technology

Frequently Asked Questions - Excel

Problems sorting case sensitive date in Excel?
How do you create a custom list?
How to spell check the entire workbook?
Is there a way to copy only column width?
Can I enter a Date and Time on Demand?
Do you do a lot of copy-paste or cut-paste moves in Excel?
Can you sum it up in Excel with just 2 key strokes?
Are you tired of writing the formula for adding the values of cells?

Problems sorting case sensitive data in Excel?
Ever find that you have case sensitive data (part numbers, account numbers, etc.) that just don't seem to sort properly? Does Excel ignore upper and lower case and sort purely on letter content?

Highlight the list to sort. Go to the Data menu and select Sort. When the Sort window appears on the screen, locate and click the Options button. Once in the Sort Options window, check the Case Sensitive box. Click the OK button. Finish setting your options and click the OK button.

This time the sorted data should be sorted in a whole new light. You will no longer have to rearrange by hand...

How do you create a custom list?
What is a custom list? Have you ever noticed that when you type in a cell and then use the fill handle (the little box in the lower right corner of the cell highlighted) to drag down you get the rest filled in? Usually the fill handle will copy data if there's only one entry. This time it didn't. What's going on here?

The short answer is that it is in custom list. So, what's a list? Basically, it is a set of data that have been designated as belonging together. Obviously, the days of the week or months of the year would qualify, but there are probably others sets of information you find yourself constantly entering, such as a list of names, part numbers, departments, etc. Wouldn't it be nice to have these in a list and not having to type all the items or copy/paste.

You have several options for the creation of a list:

• First option: PC Users - Go to Tools on the menu bar and select Options. The Options window opens and click the Custom Lists tab. Mac Users - Go to Excel on the menu bar and select Preferences. The Preferences window opens and click Custom Lists. Click the New List, type the entries in the List Entries box (the first character cannot be a number), press Return to separate each entry, click the Add button when you have completed your list, and at this point you can create another new list or click OK.
• If you have a list already entered into a worksheet and would like to added to the Custom Lists - Good news! I have got just the thing for you. Highlight the entire list on your worksheet. Now - PC Users - Go to Tools on the menu bar and select Options. The Options window opens and click the Custom Lists tab. Mac Users - Go to Excel on the menu bar and select Preferences. The Preferences window opens and click Custom Lists. If you take a look at the bottom of the Custom Lists tab, you will see a field at the bottom labeled as "Import List from Cells." This field should now contain the address of the highlighted cells. Click the Import button. Immediately the data should be added in the Custom Lists pane and displayed in the List Entries pane. Click Add, you can create another new list or click and OK.
• If you forgot to highlight the data, do you see the little button at the end of the "Import List from Cells" field? It looks like a miniature worksheet with a red arrow (PC users) or triangle (Mac users). Click it. You will be taken out into the worksheet where you can highlight the list. When you are done, you will need to click the small button at the far right of the "Import List from Cells" window that opened when you jumped back out to the worksheet. Excel will then return you to the Options window where you can continue by clicking Import, then Add and OK.

How to spell check the entire workbook?
Did you know that if you run Spell Check it only checks the current worksheet not the entire workbook? Want to check the whole thing at once? Then select all worksheets before you run the Spell Check. Looking for a fast way to select all worksheets in the workbook? Simply right click on a sheet tab (PC users) or Ctrl + click the mouse on a sheet tab (Mac users) and choose Select All Sheets from the menu that pops open. Now go to Tools on the menu bar and select Spelling.

Is there a way to copy only column width?
The first step is to have a cell selected in the column that has the width you want to transfer. Copy that cell (Ctrl+C or click Edit on the menu bar and select Copy). Now select a cell in the column that needs the new width. Here you are looking to use Paste Special by clicking on Edit on the menu bar and select Paste Special. The Paste Special dialog box will appear on your screen, in the Paste section select Column Width and click OK. The width of the new column is altered without transferring any of the data or other formatting from the copied cell.

Can I enter a Date and Time on Demand?
Do you find yourself constantly entering the date and/or time in Excel? Wish you had a quick key combination to get the job done for you? Next time you are in a cell and need to insert the current date try this combination - Ctrl + ; (semicolon). And if you need the time try this combination - Ctrl + Shift + : (colon).

Do you do a lot of copy-paste or cut-paste moves in Excel?
First you will need to highlight the data and either cut or copy as needed.

Then go to the new place where the data is to be inserted, selecting the cell just below the insertion point. Now right click (or for Mac Users Ctrl+click). From the menu that pops open, select "Insert Copied/Cut Cells." The Insert Paste window will appear where you will have to decide how you want the existing data to be moved. You can either shift right or down.

If you shift right then the data in ONLY the rows to be affected by the paste will all shift right just far enough to accommodate the pasted cells. This does not clear the whole column, so be careful when dealing with complex worksheets. You could accidentally separate related data from rows not affected by the paste that really belonged in the same column as items that were moved.

Should you choose to shift down, all the data from the selected cell and below in ONLY the affected column(s) will be moved down just enough rows to insert the pasted cells. Again, this does not move all data across the rows, only the data in the pasted columns. So be careful when using this feature.

Click OK once you have made a directional decision.

Can you sum it up in Excel with just 2 key strokes? - PC Users
Many of us are familiar with the AutoSum button in Excel. It is a handy little guy who will, with a single click, insert the formula for the sum of a column of data located just above whatever cell you currently have selected.

Those of you who prefer to use the keyboard for everything may be wondering if there is an equivalent shortcut for you. Next time the keyboard enthusiast in you use the AutoSum feature give this a try:

• Select the cell below your column of data.
• Now simply use Alt+=.

The formula to sum the column of data is inserted with the cell references in place just as if you had actually clicked the AutoSum button.

Are you tired of writing the formula for adding the values of cells?
What you are searching for is the AutoSum button( ) located on the Standard toolbar. If you don't see this icon on your screen then go to the View menu, Toolbar submenu, and select Standard from the list.

You need to have a column or row of data to be totaled. Simply select a cell below or to the right of the data. Now click the AutoSum button. If you agree with this range of data, then hit the Enter key and Excel will accept the formula.

If you don't agree with the range set by the AutoSum button then you have a couple of choices:

• Type in the range of cells to be added and hit the Enter key.
• Use the Shift key in combination with the arrow keys to highlight the cells that you need in the formula and hit the Enter key.
• Use the Ctrl key with the mouse to jump to and highlight non-adjacent section and/or click the cells to include in the formula and hit the Enter key.