Contextures Blog - Page 116 of 139 - Excel tips and tutorials

Sort Microsoft Word List-Ignore The

You don’t have to put all your lists in Excel – Word has a Sort feature too.

And Word has an extra feature that makes it easy to sort a book list — your can ignore “The”, at the start of the book title.

Book Titles List

For example, in this list, several of the book titles start with “The”.

When sorting the list, I’d prefer to ignore that word, and sort by the second word in the title. (Picture books for 2-year-old boys sure have interesting titles!)

WordBookList

Format as Hidden Text

I’ll format those “The” words as hidden text, before sorting.

Select all the titles in the book list.
On the Ribbon’s Home tab, click Replace
In the Find What box, type ‘The ‘, including a space character, and with an upper case T.
Click the More>> button, to see the options.
Add a check mark to Match case
In the Replace With box, type ‘The ‘, including a space character, and with an upper case T.

Format Settings

Click the Format button, and click Font.
All the Effects should have grey check marks.
Click Hidden, to add a black check mark.

TextHidden

Click OK to close the Replace Font dialog box.
Click Replace All, to replace the text, and click any confirmation messages that appear.
Close the Find and Replace dialog box.

Sort the List

When the text is hidden, and hidden text is not visible, that text will be ignored when sorting.

With all the book titles selected, click the Sort button on the Ribbon’s Home tab.

WordSortRibbon

Click OK to sort the list.

Note: In Excel 2003 and earlier versions, the Sort command is on the Table menu.

Show All Hidden Characters

To toggle the hidden characters on and off, use the keyboard shortcut:

Ctrl + * (Ctrl + asterisk)

Note: This is an asterisk, and you may have to use the Shift key with the Ctrl key.

Show the Hidden Text

If you’d like to see the full book titles, including the hidden text, but not the other hidden characters, you can change an option in Word.

Click the Office button, then click Word Options.
Click the Display category, and add a check mark to Hidden text, in the Always show these formatting marks on the screen section.

WordOptionsHidden

Note: In Word 2003 and earlier versions, the Hidden Text check box is found in Tools>Options, View tab.

The characters formatted as Hidden Text will have a dotted underline.

WordHiddenTextUnderline

________________

Data Validation Percentage Retry

Today I heard from someone who was having problems with data validation, in cells that were formatted in Percent Style.

Enable automatic percent entry

In the workbook, Enable automatic percent entry is turned on (Office button, Excel Options, Advanced, Editing options).

Data Entry Cells

In the cells that are formatted as Percent Style, the percent sign automatically appears as you start typing a number.

NumberPctSign

Data Validation Rule for Decimals

The data validation allows Decimals between zero and 100.

DataValDecimal

Invalid Entries

All goes well if you enter a valid number in the formatted cells. However, if you enter text, or an invalid number, the Data Validation error message appears.

DataValError

Data Validation Retry

If you click Cancel, the cell is cleared. You can type another value in the cell, and the percent sign is automatically added.

However, if you click the Retry button, the cell isn’t cleared. The existing entry, including the percent sign, is highlighted.

When you type a new number, it replaces the existing entry, and Excel doesn’t automatically add a percent sign.

The result is a percentage much higher than what you intended. Here, it’s 5500% instead of 55%

Retry Percentage Workaround

If you use the Retry button, remember to type the percent sign yourself.

Or, click Cancel, to start a new entry.

As a reminder, you could add those instructions to your Data Validation error message.

Or, turn off the Enable automatic percent entry option, and always add a percent sign if typing a whole number in these cells.

There’s more information on the Enable automatic percent entry option in the Microsoft Knowledgebase.
__________________

Turn Off Pivot Table Subtotals

When you add field to the Row or Column area of a pivot table, subtotals automatically appear in the outer fields. If you want to turn off some of the subtotals, keep reading!

Continue reading “Turn Off Pivot Table Subtotals”

How to Change Excel Comment Shape

In earlier versions of Excel you could insert a comment on the worksheet, and then use the Drawing toolbar commands to change the comment’s rectangle to a different shape.

It’s not a technique that you’d want to use too often, but it can add impact to a worksheet if used sparingly.

Change Shape Command not on Ribbon

In Excel 2007, the Drawing Tools Format tab doesn’t appear on the Ribbon when you select a comment, so the Change Shape command isn’t available.

It’s still possible to change the comment’s shape, but it takes a bit more effort.

There are written steps, and a short video, below

Add Change Shape Command

The first step is to add the Change Shape command to the Quick Access Toolbar (QAT):

Right-click the QAT, and click Customize Quick Access Toolbar
At the top, in the Choose commands from drop down list, click Drawing Tools | Format Tab
Then, in the list of commands, click Change Shape, shown in the screen shot below
Next, click the Add button, to put the command on the QAT
Finally, click the OK button, to close the Excel Options window

in the list of commands, click Change Shape

Change the Comment Shape

After you’ve added the Change Shape command to the QAT, it’s easy to change a comment shape (or any other shape on the worksheet.

To change a comment’s shape, follow these steps:

First, select the cell that contains the comment
Next, on the Ribbon, click the Review tab, and click Edit Comment
Click the border of the comment, to select the shape.
- The selected shape should have a thick grey border, like the one in the screen shot below.
Finally, on the QAT. click the Change Shape command, and click on the shape that you want to use.

CommentChangeShape

Watch the Video

To see the steps for adding the Change Shape command, and changing the comment shape, you can watch this short Excel video tutorial.

______________

Identify a Contact in Outlook

Occasionally you might get an email in which the sender mentions someone else. In the example shown below, the client promises to ask Xavier to contact you.

That name sounds vaguely familiar, but perhaps you can’t remember exactly who Xavier is, or what role he has in the client’s company.

If you’re using Outlook 2007, it can help you remember.

Who Is…

Right-click on a name in the body of an email, to open a shortcut menu.
Click Who Is…, and Outlook will check your contact information, to see if there’s anyone with that name.

OutlookWhoIs

If there’s more than one person with that name, a Check Names dialog box will open, with a list of possible matches.

OutlookWhoIsList

Click on a name in the list, then click OK, to open that person’s Contact information.

If there’s only one person with the name, their Contact information will open automatically.
________________

Create Conditional Formatting Icons in Excel

In Excel 2007, conditional formatting options include Icon Sets, such as coloured flags, stop lights, and other symbols.

These icon sets aren’t available in earlier versions of Excel, but here’s how you can create your own conditional formatting icons in Excel 2003.

TIP: You can use this technique in newer versions of Excel too, if you can’t find an existing icon set with the shapes and colours that you need

Continue reading “Create Conditional Formatting Icons in Excel”

Excel Column Headings Show Numbers-How to Fix

Have you ever opened an Excel workbook and found that the column headings show numbers instead of letters?

The formula look strange too, showing references like RC[-1] instead of D2.

Continue reading “Excel Column Headings Show Numbers-How to Fix”

Safely Sorting Data in Excel

It seems simple enough, but sorting data in Excel can go horribly wrong, if you aren’t careful.

For example, you could sort a list of names, but miss the phone numbers in a nearby column. Then, when you’re finished sorting, all the names are associated with the wrong phone number.

Trouble Free Excel Sorting

Here are some tips for trouble free sorting in Excel:

Make a backup copy of your file before you sort the list. If there are problems with the sorted list, you can use a copy of your backup file instead.
Save the file before you sort the list. Then, if the list get scrambled, you can close the list without saving it again.
Before you sort, select all the cells in the list. This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list — but not always. If there are blank rows or columns, some of the data may not be included in the sort, and the list will be scrambled.

Sort Excel Data by One Column

In some lists, you might want to sort by one column. For example, in a list of sales orders, you could sort by Order Number.

Select all the cells in the list. Tip: Use the keyboard shortcut Ctrl + A.
On the Ribbon, click the Data tab, then click Sort.

DataSortCommand

From the Sort by dropdown, select the column you want to sort.
Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.

From the Sort On drop down, select Values.
From the Order drop down, select A to Z, or Z to A.
Click OK

Sort Excel Data by Two or More Columns

Sometimes you need to sort by multiple columns.

For example, in a list of sales orders, you could sort by Customer Name and then by Order Date.

The customers would be sorted alphabetically, and if there are multiple orders for a customer, those orders would be sorted by date.

Follow steps 1 to 5 in the Sort Excel Data by One Column instructions above, to sort by Customer Name.
Click Add Level

SortAddLevel

From the Then By drop down, select the second column that you want to sort – Order Date in this example.

SortTwoLevels

Add other levels, if necessary.
Click OK

_______________________

Worksheet Data Entry Form in Excel

With a bit of programming, you can make it easy for users to enter data in an Excel workbook, and keep them away from the stored data.

Sample Data Entry Form

Dave Peterson created a sample workbook with a worksheet data entry form.

Data Entry Features

In the worksheet Data Entry form, there are data validation drop down lists in cells D5 and D7.

The quantity number is typed in cell D9.

After you fill those 3 cells, click the Add to Database button.

A macro runs, and your new record is added at the end of the database, which is a list stored on a different sheet.

View the Stored Records

In Dave’s workbook, you could click the View Database button to go to the database sheet, and review or edit the existing order records.

Worksheet Scroll Buttons

In some cases you might prefer to hide the database sheet, to protect the records, but still allow users to view the existing data.

I’ve added a few buttons to Dave’s workbook, to allow users to scroll through the existing records.

FormDataScroll

Navigation Buttons

The navigation buttons take you to the first, previous, next or last record.

Or, you can type a record number in the yellow cell, to go to a specific record.

Go To Database Button

In the updated workbook, I left the “Go To Database” button on the data entry worksheet.

You could remove that button, and hide the PartsData sheet, if you prefer, so users are less likely to change the data.

Download the Sample File

The zipped sample workbook can be downloaded from the Data Entry Worksheet Form page on my website.

In the Download section, look for Version 2 – Navigation Arrows.

Limit the Total Amount Entered in Excel

You can use Excel’s Data Validation to limit the total amount that users enter in a group of cells.

For example, if budget is $3500, you can prevent entries that will go over that total amount.

Budget Sheet Setup

Here’s what is on the Budget data entry sheet:

Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal.
The user can enter budget amounts in cells C3:C7.
Cell C8 sums the amounts entered in cells C3:C7.
Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8).

Add the Data Validation

On the Budget Data Entry Sheet, data validation is applied to cells C3:C7, because this is where the data entry occurs.

Select cells C3:C7
On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)

DataValClick

Choose Allow: Custom
For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to the amount in the BudgetTotal cell.
=SUM($C$3:$C$7)<=BudgetTotal
Click OK to close the Data Validation dialog box.

DataValTotalLimit

Test the Data Validation

After setting up the data validation, you can test it, to make sure that it works as expected.

In cell C7, enter $900, which is higher than the amount remaining.
Press Enter, and you should see an error message.
Click Cancel or Retry, and enter a valid amount.

DataValBudgetError
_______________________