5- Advanced Formatting

(Open Exercise file) Back To Excel Index Page Back To Edmond Home Page

51- Removing Duplicates Play Video

1) Click a cell in the table. 

2)  Click the Design tab under Table Tools. 

3) Click the Remove Duplicates button.

4) Select the columns with duplicates. 
You can click Select All or Unselect All.

52- What is Conditional Formatting? Play Video

  Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on a condition (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition

53- Working with Conditional Formatting Play Video

1) Select a cell or a range. 

2) Click the Home tab. 

3) Click the Conditional Formatting button. Point to Data Bars. 

4) Click the colored data bar: Blue, Green, Red, Orange, Light Blue, or Purple.

5) To create a custom data bar, click More Rules

6) Specify the options. Then click OK.

7) Check the result.

Clear Conditional Formatting Rules:

1) Select the cell or a range. 
           Choose the clear rule options: 
           Clear Rules from Selected Cells, 
           Clear Rules form Entire Sheet, 
           Clear Rules from This Table, 
           Clear Rules from This PivotTable.

2) Check the result.

1-

54- Managing Conditional Formatting Rule References Play Video

1) Select the cell or a range. 

2) Click the Home tab. 

3) Click the Conditional Formatting button. 

4) Then click Manage Rules.

5) Click the Show formatting rulers for list arrow. 
           Choose an option to show the rules.

6) To change the rule in precedence, click Move Up or Move Down.

7) To stop rule evaluation for a specific rule, select the Stop If True.

8) To delete a rule, click Delete Rule.

9) To edit a rule, click Edit Rule. Click OK.

10) Check the result.

11)

55- Converting text to columns Play Video

Sometimes you like to grip your information included in one column in your worksheet and break it into two.

Split content based on a delimiter:

Use this method if your names have a delimited format, such as “First_name Last_name” (where the space between First_name and Last_name is the delimiter) or “Last_name, First_name” (where the comma is the delimiter).

Split space-delimited content

To complete these steps, copy the following sample data to a blank worksheet.

How to copy an example

1) Create a blank workbook or worksheet.

2) Select the example in the Help topic.

Note    Do not select the row or column headers.

3) Press CTRL+C.

4) On the worksheet, select cell A1, and then press CTRL+V.

5) To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1 2 3 4

A Syed Abbas Molly Dempsey Lola Jacobsen Diane Margheim

1) Select the range of data that you want to convert.

2) On the Data tab, in the Data Tools group, click Text to Columns.

3) In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.

4) In Step 2, select the Space check box, and then clear the other check boxes under Delimiters.

The Data preview box shows the first and last names in two separate columns.

5) Click Next.

6) In Step 3, click a column in the Data preview box, and then click Text under Column data format.

Repeat this step for each column in the Data preview box.

7) If you want to insert the separated content into the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).

Important:  If you do not specify a new destination for the new columns, the split data will replace the original data.

8) Click the icon to the right of the Convert Text to Columns Wizard.

9) Click Finish.

Split comma-delimited content:

To complete these steps, copy the following sample data to a blank worksheet.

How to copy an example

1) Create a blank workbook or worksheet.

2) Select the example in the Help topic.

 Note    Do not select the row or column headers.

3) Press CTRL+C.

4) On the worksheet, select cell A1, and then press CTRL+V.

5) To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formula tab, in the Formula Auditing group, click the Show Formulas button.

1 2 3 4

A Abercrombie, Kim Cavaglieri, Giorgio Ito, Shu Philips, Carol

1) Select the range of data that you want to convert.

2) On the Data tab, in the Data Tools group, click Text to Columns.

3) In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.

4) In Step 2, select the Comma check box, and then clear the other check boxes under Delimiters.

The Data preview box displays the first names and last names in two separate lists.

5) Click Next.

6) In Step 3, click a column in the Data preview box, and then click Text under Column data format.

Repeat this step for each column in the Data preview box.

7) If you want to show the separated content in the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).

Important:  If you do not specify a new destination for the new columns, the divided data will replace the combined data.

8) Click the icon to the right of the Convert Text to Columns Wizard.

9) Click Finish.

Split cell content based on a column break:

You can also customize how you want your data to be separated by specifying a fixed column break location.

1) Select the cell or range of cells.

2) On the Data tab, in the Data Tools group, click Text to Columns.

3) In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next.

4) In the Data preview box, drag a line to indicate where you want the content to be divided.

Tip To delete a line, double-click it.

5) Click Next.

6) In Step 3, select a column in the Data preview box, and then click a format option under Column data format.

Repeat this step for each column in the Data preview box.

7) If you want to show the split content in the columns next to the full name, click the icon to the right of the Destination box, and then click the cell next to the first name in the list.

Important: If you do not specify a new destination for the new columns, the divided data will replace the original data.

8) Click the icon to the right of the Convert Text to Columns Wizard.

9) Click Finish.

56- Data validation Play Video

Hide

In many worksheets that you create, users will enter data to get the desired calculations and results. Ensuring valid data entry is an important task. You may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Providing immediate help to instruct users and clear messages when invalid data is entered is also essential to make the data entry experience go smoothly.

Once you decide what validation you want to use on a worksheet, you can set up the validation by doing the following:

1) Select one or more cells to validate.

2) On the Data tab, in the Data Tools group, click Data Validation.

The Data Validation dialog box is displayed.

3) Click the Settings tab.

4) To specify the type of validation that you want, do one of the following:

Allow values from a list:

1) In the Allow box, select List.

2) Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default).

For example:

§ To limit entry to a question, such as "Do you have children?” to two choices, type Yes, No.

§ To limit a vendor's quality reputation to three ratings, type Low, Average, High.

You can also create a list of values from a range of cells. For more information, see Create a drop-down list from a range of cells.

3) Make sure that the In-cell dropdown check box is selected.

Allow a whole number within limits:

1) In the Allow box, select Whole Number.

2) In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.

3) Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.

Allow a decimal number within limits:

1) In the Allow box, select Decimal.

2) In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.

3) Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.

Allow a date within a timeframe:

4) In the Allow box, select Date.

5) In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.

6) Enter the start, end, or specific date to allow. You can also enter a formula that returns a date.

For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Minimum box, and enter =TODAY()+3 in the Maximum box.

Allow a time within a timeframe:

1) In the Allow box, select Time.

2) In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.

3) Enter the start, end, or specific time to allow. You can also enter a formula that returns a time value.

For example, to set a time frame for serving breakfast between the time when the restaurant opens in cell H1 and 5 hours after the restaurant opens, select between in the Data box, enter =H1 in the Minimum box, and then enter =H1+"5:00" in the Maximum box.

Allow text of a specified length:

1) In the Allow box, select Text Length.

2) In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.

3) Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.

For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

Calculate what's allowed based on the content of another cell:

1) In the Allow box, select the type of data that you want.

2) In the Data box, select the type of restriction that you want.

3) In the box or boxes below the Data box, click the cell that you want to use to specify what's allowed.

For example, to allow entries for an account only if the result won't go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.

Use a formula to calculate what's allowed:

1) In the Allow box, select Custom.

2) In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example:

To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

Optionally, display an input message when the cell is clicked.

How?

1) Click the Input Message tab.

2) Make sure the Show input message when cell is selected check box is selected.

3) Fill in the title and text for the message.

Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How?

1) Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

2) Select one of the following options for the Style box:

§ To display an information message that does not prevent entry of invalid data, select Information.

§ To display a warning message that does not prevent entry of invalid data, select Warning.

§ To prevent entry of invalid data, select Stop.

3) Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Test the data validation to make sure that it's working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip If you make changes to the validation in one cell, you can automatically apply your changes to all other cells that have the same settings.

Back To Top Back To Excel Index Page (Open Exercise file)

Back To Edmond Home Page

Copyright © Edmond Hakmeh 2009. All Rights Reserved