OTHER FEATUERS IN EXCEL

CONDITIONAL FORMATTING

Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s
value. For example, highlighting positive values with green background color, and negative
values with red.

Apply Conditional Formatting to a Cell

In this example, you'll set conditional formats so that a

cell:

 turns blue if it contains a value higher than 75

and
 turns green if it contains a value lower than 50.

Follow these steps to apply conditional formatting to

cells:
1. Select the cells to be formatted. In this example,
cells B2:B4 are selected.
2. On the menu bar, choose Format>Conditional
Formatting...

3. In the Conditional Formatting dialog box, leave the first drop-down box set to Cell
Value Is
4. In the second drop-down box, choose one of the operators. In this example, choose
'greater than'
5. In the text box, type a number or a cell reference. In this example, type the value
you want to check -- 75.

6. Click the Format button

7. On the Patterns tab, select a colour for the

1
conditional formatting. In this example, a
medium blue fill colour is selected.
You can also choose a Font format or a cell
Border.
8. Click OK, to close the Format Cells dialog
box.

9. If you want to add another conditional format, click the Add button.
10. Repeat steps 3 to 8, using the values and colours that you want for the second conditional
format.
11. Click OK, to return to the worksheet.

In the example shown at the right, cell B2 contains a value of 76, so it is formatted with a blue
fill colour, because it's greater than 75. Cell B4 is green, because it's value is less than 50.

SORTING

Sorting lists is a common spreadsheet task that allows you to easily reorder your data. The most
common type of sorting is alphabetical ordering, which you can do in ascending or descending
order.

2
To sort in alphabetical order:
 Select a cell in the column you want to sort (In this example, we choose a cell in
column A).
 Click the Sort & Filter command in the Editing group on the Home tab.
 Select Sort A to Z. Now the information in the Category column is organized in
alphabetical order.

You can Sort in reverse alphabetical order by choosing Sort Z to A in the list.

To sort from smallest to largest:

 Select a cell in the column you want to sort (a column with numbers).
 Click the Sort & Filter command in the Editing group on the Home tab.
 Select From Smallest to Largest. Now the information is organized from the
smallest to largest amount.

You can sort in reverse numerical order by choosing From Largest to Smallest in the list.

To sort multiple levels:

 Click the Sort & Filter command in the Editing group on the Home tab.
 Select Custom Sort from the list to open the dialog box.
OR
 Select the Data tab.
 Locate the Sort and Filter group.

3
 Click the Sort command to open the Custom Sort dialog box. From here, you
can sort by one item or multiple items.

 Click the drop-down arrow in the Column Sort by field, then choose one of the
options—in this example, Category.

 Choose what to sort on. In this example, we'll leave the default as Value.
 Choose how to order the results. Leave it as A to Z so it is organized
alphabetically.
 Click Add Level to add another item to sort by.

4
  Select an option in the Column Then by field. In this example, we chose Unit
Cost.
 Choose what to sort on. In this example, we'll leave the default as Value.
 Choose how to order the results. Leave it as smallest to largest.
 Click OK.

The spreadsheet has been sorted. All of the categories are organized in alphabetical order, and
within each category the unit cost is arranged from smallest to largest.

5
FILTERING CELLS

Filtering, or temporarily hiding, data in a spreadsheet is simple. This allows you to focus on
specific spreadsheet entries.

To filter data:
 Click the Filter command on the Data tab. Drop-down arrows will appear beside
each column heading.

 Click the drop-down arrow next to the heading you would like to filter. For
example, if you would like to only view data regarding Flavors, click the drop-
down arrow next to Category.

6
  Uncheck Select All.
 Choose Flavor.
 Click OK. All other data will be filtered, or hidden, and only the Flavor data is
visible.

To clear one filter:

 Select one of the drop-down arrows next to a filtered column.
 Choose Clear Filter From...

7
To remove all filters, click the Filter command.

Filtering may look a little like grouping, but the difference is that now you can filter on another
field if you want to. For example, let’s say you want to see only the vanilla-related flavors. Just
click the drop-down arrow next to Item, then select Text Filters. From the menu,
choose Contains because you want to find any entry that has the word vanilla in it. A dialog box
appears. Type vanilla, then click OK. Now we can see that the data has been filtered again and
that only the vanilla-related flavors appear.

PRINTING IN EXCEL

To view the spreadsheet in Print Preview:

 Left-click the Microsoft Office button.
 Select Print.
 Select Print Preview. The spreadsheet will appear in Print Preview view.

8
Click the Close Print Preview button to return to the Normal View.

To make previewing your spreadsheet easier, add the Print Preview command to the Quick
Access toolbar.

Exploring Print Preview

Once you are in Print Preview, you can access many of the same features that you can access
from the Ribbon; however, in Print Preview you can see how the spreadsheet will appear in
printed format.

9
To modify margins, column width, or row height while in Print Preview:
 Click the Print Preview command on the Quick Access toolbar, or select Print
Preview from the Microsoft Office button menu. The spreadsheet opens in Print
Preview mode.
 Hover your cursor over one of the black margin markers until a double
arrow appears.

 Left-click and drag the marker to the desired location. The change will be
reflected in the spreadsheet.

To modify margins:
 Select the Page Layout tab.
 Left-click the Margins command.

10
  Choose one of the pre-defined settings, or enter custom margins.

To change page orientation:

 Select the Page Layout tab.
 Left-click the Orientation command.
 Select either Portrait or Landscape.

Portrait orients the page vertically, while Landscape orients the page horizontally.

To use Scale to Fit:

 Select the Page Layout tab.

11
  Locate the Scale to Fit group.
 Enter a specific height and width, or use the percentage field to decrease the
spreadsheet by a specific percent.

Scale to Fit is a useful feature that can help you format spreadsheets to fit on a page. However,
be careful with how small you scale the information—it can become difficult to read!

To change the paper size:

 Select the Page Layout tab.
 Click the Size command.
 Select a size option from the list.

To define a print area:

 Left-click, then drag your mouse to select the cells you wish to print.
 Click the Print Area command.
 Choose Set Print Area.

 Now, only the selected cells will print. You can confirm this by viewing the
spreadsheet in Print Preview.

To return to the default setting, which is the entire worksheet, click the Print Area command,
then select Clear Print Area.

To insert a break:
 Select a cell below where you want the break to appear.
 Select the Breaks command.
 Select Insert Break.

12
Click Print Preview to confirm that the break appears in the correct place in your spreadsheet.

To use the Print Titles command:

This is an important command to be familiar with if you intend to print your worksheets. It
allows you to select specific rows and/or columns that will be repeated on each printed sheet.
Imagine how difficult it would be to read page 48 of a printed spreadsheet if the column and row
headings only appeared on the first page.

 Select the Page Layout tab.

 Click the Print Titles command. The Page Setup dialog box appears.
 Click the icon at the end of the field.

 Select the first row in the spreadsheet that you want to appear on each printed
page.

 Repeat for the column, if necessary.

 Click OK.

13
To print from the Microsoft Office button:
 Left-click the Microsoft Office button.
 Select Print Print. The Print dialog box appears.

 Select a printer if you wish to use a printer other than the default setting.
 Click Properties to change any necessary settings.
 Choose whether you want to print specific pages, all of the worksheet, a selected
area, the active sheet, or the entire workbook.
 Select the number of copies you'd like to print.
 Click OK.

14