Excel cell color: Coloring and shading cell backgrounds

cell_color_1

YOUR OBJECTIVE

You already formatted your Excel table. Now you want to add color to your table by using background colors, i.e., shading.

In this learning module “excel cell color”, you will find out how to fill the backgrounds of cells and cell ranges with colors.

 

HOW DOES EXCEL CELL COLOR WORK?

First select the cell or cell range that you want to fill with background color. Then click the small black arrow on the Fill Color button (Home tab, Font group).

The Theme Colors catalog will appear. Here you can select the color of your choice by clicking on it. The background(s) of the selected cell(s) will now be filled with the selected color:

 

HINT

If you do not see a color you like, click .

If you want to control the fill-color characteristics more closely, click the dialog box launcher on the Home tab, in the Font group.

 

The Format Cells dialog box will appear. Now go to the Fill tab.

 

In the Background Color area, you will find a variety of colors. Click on the color you want to use.

If you are looking for a special color, you can find a greater variety or mix a color yourself by clicking More Colors…

In the Pattern Color and Pattern Style selection lists, you can specify a pattern style to serve as a background and color it as well.

To use a special color effect (i.e., graduated shading) on cell backgrounds, click Fill Effects…

If you do, the Fill effects dialog box will appear:

 

In the excel cell Colors area, you can specify how many colors you want to use for the fill effect.

Select the excel cell colors you want to use in the Color 1 and Color 2 selection lists.

Use the Shading styles area to select a look that suits your purpose.

For some shading styles, you can also apply a Variants option.

In the Sample area, you can see the result of your current settings.

When satisfied with your settings, click OK.

 

Excel cells: Working with cell references in the same worksheet

Excel cells: Working with cell references in the same worksheet

Excel cells: YOUR OBJECTIVE

In an Excel worksheet, you recorded your company’s sales figures from various locations. To obtain a clearer picture of the North American sales, you want to create a separate table on the same worksheet that displays the sales numbers from only the North American locations. You want this table always to display the current sales data from the original table, even if those numbers change. To accomplish this, excel cells reference must be used to link the cells in the new table to the cells in the source table

In this learning module, you will be shown how to creaate and use cell references to link to cells in one table to cells in another table, when both tables are located in the same worksheet .

Excel cells: HOW DOES IT WORK?

In  Excel cells, you want to make use of the contents of another cell, located elsewhere in the same worksheet. Using a excel cell reference is the easiest way to accomplish that. Excel cell references offer the following advantages:

  1. It is certain that the contents of the new cell are always identical to the contents of the source cell.
  2. You are spared typing and therefore possible typing errors.
  3. If the value in the original cell changes, the contents of the linked cell will automatically update, too. You can therefore be certain that in all cells that refer to the source cell, you are always working with the most current data.

In our current example: to create a table for the North American market (Detroit, Boston), click in cell A14 where the first ice-cream flavor (this company’s product) is to be used as the row heading. Now, to begin entering a cell reference, type in an equal sign (=), and then click on cell A4…

As seen in the formula bar, the entry =A4 is automatically generated:  .

Now, when you press [Enter], the excel cells reference is complete, and the content of cell A4 (“Chocolate”) appears in cell A14.

HINT

A cell reference may also be created by typing it directly into the relevant cell. In our example, you could have typed “=A4″ into the cell. This method, however, since it requires typing, is considerably more error prone.

HINT

Instead of manually entering excel cells  references for the other ice cream flavors (as in our example), you might prefer to use the AutoFill function (see learning module: Formulas and functions). To do this, position the mouse pointer over the bottom-right corner of cell A14. It will change to a black cross.Now, while holding the mouse button depressed, pull the pointer down to cell A17. When you release the mouse button, the desired cell references, properly adjusted for position, will be inserted. The individual flavors and the word “Total” will appear in their proper places.

To create cell references that will transfer the Boston column into the new table, click cell B13 and then enter an equal sign (=). Now click cell C3.

As soon as you click on cell C3 (Boston), the entry appears in the formula bar. Press [Enter] to complete the excel cells reference.

The remaining excel cells  references for the Boston column can be entered by using Excel’s AutoFill function , as described in the tip above.

Similarly, you can create the cell reference for the “Detroit” column by clicking on cell C13. Then type in an equal sign (=) and click on cell B3 . Complete this excel cells  reference, too, by clicking [Enter]. As above, you can use AutoFill to fill in the rest of the column’s cell references.

Thanks to the use of excel  cells references, any changes made to Boston or Detroit data in the source table will immediately be reflected in the new North American Market table.

HINT

To format the numbers in the new table as they apear in the source table, you must first select the relevant cells in the new table. Then, on the Home tab, in the Number group, click the arrow on the button. In the selection catalog that appears, click the Currency option. Finally, to remove two decimal positions to the right of the decimal point; in the same group, click the button twice.

Excel borders: Drawing borders around cells and cell ranges

Excel borders: Drawing borders around cells and cell ranges


YOUR OBJECTIVE

You already created an Excel table, but now want to make it easier to read and understand. To accomplish that, you want to format it by drawing various borders.
In this learning module, you will find out how to apply excel borders to cells or cell ranges. [Read more...]