ZAMBAK_IT_Excel2010

3.2.5 Fill

In Excel 2007, we have some improvements in the Fill Tab. In Excel 2003, we could use simple colors. But now, you are also able to use two color gradient fill effects and patterns. Like in the other Office objects, each cell background can be filled with patterns and effects. Secondly, you are not limited like in Excel 2003. You can use theme colors or one of the more than 16 million unique colors.

3.2.6 Protection

Before protecting the sheet, first, you should Lock/Unlock necessary cells from the Format cells dialog box. In the Protection tab, we have two properties.

Locked: Prevents the selected cells from being changed, moved, resized, or deleted. Locking cells has no effect unless the sheet is protected.

Hidden: If you check this option you will hide a formula in a cell, so that it doesn’t appear in the formula bar when the cell is selected. Hiding cells has no effect unless the sheet is protected.

After that, you can protect a sheet from the Home Tab Cells FormatProtect Sheet. When you click protect sheet, it will open a dialog box for you. In this dialog box, mainly, it asks you two questions:

1.Password

2.What to allow and protect.

By checking any of these options, you can allow users to use that property. The people who know the password can unprotect and use all the properties of the sheet. The people who don’t know the password can use only what you allowed.

Example 3.3:

Select the range of the cells that you want to unlock. Then uncheck the Locked check box from the Protection tab in the Format cells dialog box. When you select Protect sheet from the Home tab, all cells are protected except for the ones you unlocked.

Figure 3.11: Fill Tab

Figure 3.12: Format Cells

Protection

Figure 3.13: Protect Sheet dialog box

Figure 3.14: Themes: Colors. Fonts,

Effects

A theme applies to the workbook. So, you cannot use different themes for different worksheets.

As all you know, professional designers first start their documents by color, font and general designs. They spend their hours or days in design, after that, they start booklet preparation. Not all people have that professional sense of color and design; but they can be helped though.

In an effort to help users create more professional-looking documents, the Office designers incorporated a concept known as document themes. Using themes is an easy (and almost foolproof) way to specify the colors, fonts, and a variety of graphic effects in a document. And best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook.

Importantly, the concept of themes is incorporated into other Office apps. Therefore, a company can easily create a standard look and feel for all its documents. For all of that, you just prepare your document with Styles.

In order to apply a cell style, after you select your destination range, you click: Home Tab Styles group Cell Styles. It will show you the options as in Figure 3.16. You select the style that you want to apply.

Using the New Cell Style… button you can open the Style dialog box and create new styles from the selected cell formats. If you want to make further changes in your format use the Format button under the Style Name box.

Because these subjects (Themes and Styles) much involved in design, you can have a detailed reading from our MS. Word book.

3.5 Using Format Painter

Here is another very efficient tool in MS. Excel: Format Painter. After you design one of your cells to fit your needs, you can use the Format Painter to apply the same format to others.

1.Select a source range

2.Click the Format Painter button on the Home Tab Clipboard Group

3.Click on the destination

With this, you can apply the source format to destination range only once. After you select the range, if you double click on Format Painter icon, you can apply the format more than once until you press the ESC key.

3.6 Using Tables

One of the most significant new features or changes came with the Excel 2007 was the tables logic. Excel, of course, has always been able to deal with tables, but they accepted tables as columns and rows of data. But since Excel 2007, it accepts tables as a special object and has special tools to manipulate them.

3.6.1 What is table?

A table is just a rectangular range of cells that (usually) contains columns, rows and headers. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a student, an employee, or a product. Rows are also called Records.

Tables typically have a header row, at the top that describes the information contained in each column. The items in this header row which are also called Fields contain a specific piece of information which is the same for all the records. For example, if each row contains information about an employee, the columns can contain data such as name, employee number, hire date, salary, department, etc. All of these are columns or fields of the table.

Figure 3.16: Using Styles

Another method to copy format is to use Paste Special.

1

Figure 3.17a: Inserting a Table

Figure 3.18: Table AutoFormat List

3.6.2 Creating a table

To create a table, we use the Table command 1 in Insert Tab Tables

3.6.3 Table AutoFormat

AutoFormat is a built-in collection of formats: font sizes, patterns, and alignments which you can quickly apply to a table. AutoFormat lets you select from hundreds of different preset formats.

In Excel 2003, we had very few options for Table AutoFormat; there were 16 predefined Table AutoFormat options there. Now, working with tables is easier than ever. All you need is to decide the best table color and design for your document. The designers of Excel, probably, realized that such tables are widely used in Excel, and they’ve taken the concept to a new professional level. They placed hundreds of professional table color and design options.

3.6.4 Some facilities for tables

Once you designate a particular range to be a table (using the Insert TablesTable command), Excel provides you with some very efficient tools that work with the table.

For example:

You can apply attractive formatting with a single click.

You can easily insert/change summary formulas in the table’s total row.

If each cell in a column contains the same formula, you can edit one of the formulas, and the others change automatically.

Figure 3.22: Table Conditional

Formatting

Example 3.5:

Now prepare the Conditional formatting as follows.

If mark is 5 Light red fill with dark red text

If mark is 2 Light yellow fill with dark yellow text

1.Select the cell or cell range you want to apply conditional formatting to

2.Select the “Greater Than” Conditional Formatting button from the Home Tab (Figure 3.21)

3.Enter the condition as in the Figure 3.23.

Figure 3.23: Format Cells that are GREATER THAN

In Office 2003, you could have up to three conditions. But since Office 2007, you can have (theoretically) an unlimited number of conditional formatting. You can apply more than one condition to the same range. To avoid problems pay attention to not to conflict your conditions.

Questions

1.Which of the following is used to write multiple lines in a cell?

2.Which tab of the following adjusts the selected text to superscript in the Format Cell window?

3.You can move a cell by dragging when the CTRL key is held down.

6.Which of the following is not a way of changing the font size?

a.Select the cell(s), Click on the triangle near the Font Size from the Font group in the Home tab. Finally choose a proper font size.

b.Select the cell(s) and right-click the selection, select Format Cells from the popup menu, click the Font tab, choose a proper font size, and click OK.

c.Select the text and press Ctrl+Shift+F. Choose a proper font size and click OK.

d.Select the text then select Format Column width... Change the value and press OK.

7.You have four cells that you want to combine into one. How can you do this? (You can choose more than one)

a.Select the cells and click the Merge Cells button in Page Layout Tab.

b.Right click on the cells and select Merge Cells button from the Mini Toolbar.

4.Locked cells have no effect unless the worksheet is ……….?

5.Which of the following is not a numeric value? (Choose all that apply)

c.Select the cells and click the Merge Cells button in the Styles group in the Home Tab.

d.Select the cells and click the Merge Cells button in the Alignment group in the Home Tab.

8.Which symbol is used before a number to make it a label?

9.How can you rotate text in a cell?

a.Open the Format Cells Dialog boxAlignment tab. Select Text direction from the combo box below.

b.Click the Home Tab Alignment groupOrientation button and select the desired orientation.

c.Select the Home Tab Cells groupFormat button Text Direction and write the degrees in the box

d.Right-click the cell and select Text Direction from the popup menu.

10.You want to change the dates in a worksheet so that they appear as 21 Oct 04, instead of 10/21/04. How can you do this?

a.Select the cells and click the Long Date button on the Review tab.

b.You have to retype all the dates, as there is no way to reformat them.

c.Select the cells and open the Format Cells Dialog box, click the Number tab, select Date from the Category list and select the date format you want.

d.You need to call your system administrator and have him or her install the Microsoft Long Date patch for you.

11.Which is not a method for applying boldface to the selected cell range?

a.Open the Format Cells Dialog box, click the Font tab, and select Bold from the Font style list.

b.<Ctrl+B>.

c.Right-click the text and select Boldface from the Mini toolbar.

d.Click the Bold button on Styles group in the Format Tab.

12.To display additional decimal places in a cell, click the ......... button in Home tab.

a.Increase Decimal

b.Percent Style

c.Increase Indent

d.Decrease Indent

13.Which of the following statements is not true:

a.Clicking the Center button on Alignment group centers the text or numbers within the cell.

b.The Merge and Center button merges several cells into a single larger cell and centers the contents inside the cell.

c.You can change cell alignment by opening the Format Cells Dialog box and clicking the Alignment tab.

d.Cells can show, at most, 3 lines of text.