Excel2010

View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.

Developer: This tab isn’t visible by default. It contains commands that are useful for programmers. To display the Developer tab, open Excel options from Office button and then select ‘Customize Ribbon’. Place a check mark next to Developer Tab.

Add-Ins: This tab is visible only if you’ve loaded a workbook or add-in that customizes the menu or toolbars. Because menus and toolbars are no longer available in Excel, these customizations appear in the Add-Ins tab.

The appearance of the commands on the ribbon varies, depending on the width of Excel window. When the window is too narrow to display everything, the commands adapt and may seem to be missing. But the commands

are still available. Figure 1.2 shows the Home

Figure 1.3: The Ribbon when Excel’s window is made narrower

tab of the Ribbon with all controls fully visible. Figure 1.3 shows the Ribbon when Excel’s window is made narrower. Notice that some of the descriptive text is gone, but the icons remain.

If you don’t like to see this wide-spread toolbars all the time, you can (un)hide them any time using Ctrl+F1 (or double click on any tab name). When the commands are hidden, you can still access them, by just clicking on the tab name.

Figure 1.4: The Ribbon when Ctrl+F1 is pressed

1.3.1 Contextual Tabs

Some other special tools appear according to the selected items. These are called Contextual tabs. For example, when you select a picture the

“Picture tools” tab appear. Similarly, in

Figure 1.5: Contextual tabs: Chart tools

the figure next, because a chart is selected, the “Chart Tools” tabs are shown.

Spreadsheet Basics

11

1.4 Accessing the Ribbon using your keyboard

At first glance, you may think that the Ribbon is completely mouse-centric. But in fact, the Ribbon is very keyboard friendly. The trick is to press the Alt key to display the pop-up keytips. Each Ribbon control has a letter (or series of letters) that you type to issue the command. After you press the associated letters the commands are executed or related task window is opened.

Figure 1.6b: Excel 2003 shortcut keys

Figure 1.6a: Accessing the Ribbon using your keyboard

You don’t need to hold down Alt key to access key tips. Just click Alt key once then you can see and select your shortcut key.

Because new menus contain different shortcut keys, if you are used to Excel 2003 shortcut keys, you can still access them. When you press a shortcut key from Office 2003, it starts catching your shortcut key sequence. And at the end, it executes the command. For example, in Office 2003, Alt+O+C+H hides the selected columns. (Figure 1.6b)

1.5 The Shortcut menus and the Mini Toolbar

When you right click on any area, the shortcut menu is shown. The box above the shortcut menu is known as the Mini toolbar and contains commonly used tools from the Home tab. It doesn’t contain any relevant command, just those that are most commonly used for whatever is selected.

The Mini toolbar was designed to reduce the distance your mouse has to travel around the screen. It’s is especially useful when a tab other than Home is selected.

Figure 1.7:

The Shortcut menus and Mini toolbar

If you don’t like to see Mini toolbar every time you right click, you can turn it off from the Office button Excel Options. Uncheck the “Show Mini Toolbar on selection” option from General tab.

Figure 1.8: Show/Hide Mini toolbar

12	Microsoft Excel

1.6 Customizing your Quick Access Toolbar

By default, the Ribbon doesn’t include all of the commands that are available in Excel. You can add any command that you want to have a quick access (that aren’t available by default in the Ribbon or your most common commands) to QAT.

You could change all the toolbars and menu items in Office 2003. But since Office 2007, you can customize only the Quick Access Toolbar (QAT) and your custom tabs. Initially, QAT includes only: Save, Undo and Redo commands.

Figure 1.9a: Customizing QAT

To add these commands to QAT, just right click on QAT and select Customize from the menu. It will open the Excel Options window.

Here you can select the commands from the left pane and add them in QAT.

Figure 1.9b: Using Custom tabs in the Ribbon Figure 1.9c: Customizing QAT

1.7 Excel with the Numbers:

		Excel 2003	Excel 2007	Excel 2010
	Number of rows	65.536	1.048.576	1.048.576
	Number of columns	256	16.384	16.384
	Amount of memory used	1 Gbytes	Maximum allowed	Maximum allowed
	Number of colors	56	4,3 billion	4,3 billion
	Number of levels of sorting	3	64	64
	Number of levels of undo	16	100	100
	The total number of characters that can display in	1.000	32.767	32.767
	a cell
	Number of unique styles in a workbook	4.000	64.000	64.000
	Maximum number of characters in a formula	1.000	8.192	8.192

Spreadsheet Basics

13

Figure 1.10: Office button

Figure 1.11: Filling in series

Figure a

Figure b

1.8Your First Excel Application

In this section, you will create a monthly growth project for a baby. Her mother

wants to see and analyze her growth. Every month, she kept height and

weight info in an Excel workbook.

1.8.1 Creating a New Workbook

Start Excel and make sure that you have an empty workbook displayed. To

create a new, blank workbook;

press Office button and select New from the menu. It’ll show you another window with some options. Select “Blank Workbook” Finally click Create button on the right.

Or simply press Ctrl+N to open a new blank workbook.

The Baby Growth project will consist of three columns of information. Column

A will contain the month names, column B will store the height info and finally

column C will store the weight. You start by entering some descriptive titles

into the worksheet. Here’s how to begin:

Move the active cell pointer to A1 and write “Months” then press enter

Write “Height” into B1 and “Weight” in C1

Then go to A2 and write the birth month of the baby: “July”

1.8.2 Filling in the month names

After you write the first month, Select A2 again and take your mouse pointer to the bottom right corner of the active cell. You will see that your mouse pointer changes into a black line plus sign.

When you drag your mouse down, you’ll see that it’ll write all the series of the months until the cell at which you drop your mouse.

Next to months write the height and weight info into the cells as in the figure on the next page.

14	Microsoft Excel

1.8.3 Applying table format

Select the entire table and click the “Format as Table” button from Styles group in the Home tab. It will show you many different preformatted table options.

After you select one, it will ask you the location of Data for the table. Because you selected the table before you start, just click OK.

Now you have professionally designed a nice looking table.

Figure 1.13: Applying table format

1.8.4 Creating a chart from your data

Select the Months and Height columns.

Click the Column button from the “Charts” group in the Insert menu. Then select the “3-D Clustered Column” from the list.

Now select the Months and Weight columns and click Clustered Cylinder in the Charts group in the Insert menu.

Your charts are ready now

Figure 1.15: Monthly Baby Growth Charts

Figure 1.12:

Format as Table dialog box

Figure 1.14: Chart Type and Subtype

Spreadsheet Basics

15

1.8.5 Saving your document

Now you finished and you can save your document.

You can click on the “Save” button from the QAT.

Or, you can click the “Save” button from the Office button

Or, use Ctrl + S shortcut keys

It will show you the “Save As” dialog box. Write the File name (Baby Growth) and click the Save button on the bottom right corner.

Figure 1.16: Saving the document

Excel’s new file formats are

XLSX: A workbook file that does not contain macros

XLSM: A workbook file that contains macros

XLTX: A workbook template file that does not contain macros

XLTM: A workbook template file that contains macros

XLSA: An add-in file

XLSB: A binary file similar to the old XLS format but able to accommodate the new features

XLSK: A backup file

16	Microsoft Excel

Questions

1.What kind of program is Microsoft Excel?

a.Word processing

b.Spreadsheet

c.Database

d.Graphics

2.You can reach all Excel commands from the……. (Choose all that apply)

a.Ribbon

b.Menu bar

c.Quick Access Toolbar

d.Status bar

3.Which of the following commands is not in the Home tab?

a.Print Preview

b.Merge and center

c.Copy

d.Increase font size

4.Which of the following displays the cell name?

a.Formula bar

b.Status bar

c.Ribbon

d.Name box

5.Which is not the way of saving a workbook?

a.<Ctrl+F5>

b.Click Save on the Home tab.

c.Click Save from the Office button.

d.<Ctrl+S>

6.What file extension will normally be used for a file created by Microsoft Excel?

a. ppt	b. xlsb	c. xlsx		d. exe
		4
1
3		7	8	9

Answer questions 7-10 according to this figure

7. Which is used to change cell alignments?

a. 1 b. 4 c. 5 d. 7

8.Which icon is used to print a worksheet or chart?

a. 1

b. 8

c. 9

d. None

9.Which icon is used to copy the format of the cells?

a. 1

b. 3

c. 5

d. 6

Spreadsheet Basics

17

10.Which icon is used to change border style?

a. 5

b. 7

c. 8

d. 9

11.How many rows are there in an Excel 2007 document?

a.65536

b.256

c.1048576

d.16384

12.How many columns are there in an Excel 2003 document?

a.65536

b.256

c.1048576

d.16384

14.Can you use all Excel 2003 shortcuts in Excel 2007?

a.Some of them

b.Most of them

c.All of them

d.None of them

15.You can change the commands in the Ribbon.

TRUE

FALSE

13.What key is used to access Ribbon commands?

a.Ctrl

b.Alt

c.Shift

d.Ctrl+Alt

Figure 2.1: Fill series popup menu

To activate Smart Tags, choose the Office Excel Options and click the Advanced Tab. Set “Show Paste Options” in Cut Copy Paste options.

WORKSHEET AND CELL OPERATIONS

2.1 Mouse Pointers

There are different mouse pointers when working with Excel.

The Select mouse pointer is used to select a cell or a range.

Select Column/Row is used to select rows or columns.

Unhide (show) a hidden row or column.

Copy/Fill series When you see this type of mouse pointer, and drag your mouse while right button pressed, a popup menu similar to Figure 2.1 will appear next to the last cell:

1. Copy cells: Copies and applies the format of the source cell to destination cells

2.Fill series: While applying the source format, automatically defines incrementation in the source, and fills series.

3.Fill formatting only: Just applies the formatting of the source cell(s) to the destination

4.Fill without formatting: Fill series or copy without formatting.

Move mouse pointer is used to move a range. Select the range that you want to move, then position your mouse pointer over a cell corner. When you see this type of mouse pointer drag it to the place that you want to move to. If the destination cells have data in them, Excel will prompt to overwrite.

Resize is used to resize columns or rows

20	Microsoft Excel