Microsoft Excel 2007 Beginners Training

Created & Presented By:

Abishek Mittal, NMP XXVI MDI, Gurgaon

Course Contents
Overview of Excel 2007 Cell References, Formatting Tools Common Formula Applications Pivot Tables & Pivot Charts Graphs, Statistical Tools Intro to Macros & Visual Basic
Excel for Beginners By Abishek Mittal 2

Excel 2007 Overview

Overview: Where to begin?

Youve been asked to enter data in Excel 2007, but youve never worked with Excel. Where do you begin? Or perhaps you have worked in Excel but still wonder how to do some of the basics like entering and editing text and numbers, or adding and deleting columns and rows. Here youll learn the skills you need to work in Excel, quickly and with little fuss.

Excel for Beginners By Abishek Mittal

The Ribbon
The band at the top of the Excel 2007 window is called the Ribbon.

The Ribbon is made up of different tabs, each of which is related to specific kinds of work that people do in Excel. You click the tabs at the top of the Ribbon to see the different commands available on each tab.

Excel for Beginners By Abishek Mittal

Workbooks and worksheets

When you start Excel, you open a file thats called a workbook. Each new workbook comes with three worksheets into which you enter data.

Shown here is a blank worksheet in a new workbook. Number of Sheets appearing can be set by using Excel options under the Microsoft Office button. Scrolling through different Worksheets Ctrl + Pg Dn Use Ctrl + Pg Up /

Excel for Beginners By Abishek Mittal

Columns, rows, and cells

Worksheets are divided into columns, rows, and cells. Thats the grid you see when you open up a workbook.

Columns go from top to bottom on the worksheet, vertically. Each column has an alphabetical heading at the top. Rows go across the worksheet, horizontally. Each row also has a heading. Row headings are numbers, from 1 through 1,048,576. The row and column headings combine to form the cell address / cell reference. For example, the cell at the intersection of column A and row 3 is called cell A3.
Excel for Beginners By Abishek Mittal 7

Cells are where the data goes

The outlined cell, highlighted column and row headings, and appearance of the cell reference in the Name Box make it easy for you to see that C5 is the active cell.
These indicators arent too important when youre right at the top of the worksheet in the very first few cells. But when you work farther and farther down or across the worksheet, they can really help you out.

Excel for Beginners By Abishek Mittal

Cell Referencing and Formatting Tools

Edit data and revise worksheets

Everyone makes mistakes. Even data that you entered correctly can need updates later on. Sometimes, the whole worksheet needs a change.
Suppose you need to add another column of data, right in the middle of your worksheet. This slide shows how easy it is to edit data and add and delete worksheet columns and rows.

Excel for Beginners By Abishek Mittal

10

Edit Data
Say that you meant to enter Peacocks name in cell A2, but you entered Buchanans name by mistake. Once you spot the error, there are two ways to correct it.
Double-click a cell to edit the data in it. Or, after clicking in the cell, edit the data in the Formula Bar. Shortcut Key to perform same action is F2 key. After you select the cell by either method, the worksheet says Edit in the status bar in the lower-left corner.
Excel for Beginners By Abishek Mittal 11

Insert a column or row

After entering data, you may find that you need to add columns or rows to hold additional information.

To insert a single column: 1. Click any cell in the column immediately to the right of where you want the new column to go. 2. On the Home tab, in the Cells group, click the arrow on Insert. On the dropdown menu, click Insert Sheet Columns. A new blank column is inserted.
Excel for Beginners By Abishek Mittal 12

Freezing Rows & Columns

Freezing a row or column lets you keep headings visible as you work with the data in a large worksheet View > Windows > Freeze Panes

Excel for Beginners By Abishek Mittal

13

Highlighting Duplicates
Select the column you want to search for duplicates In the Styles group on the Home tab, click the Conditional Formatting button, point to Highlight Cells Rules, and then click Duplicate Values Click the values with arrow, then click Custom Format In the Format Cells dialog box, set the formatting you want to use

Excel for Beginners By Abishek Mittal

14

Cell References in Formulas & Functions

> Relative > Absolute > Mixed

Entering References in Cells Select the cell reference you want to change Press the F4 key to cycle the reference from relative to absolute to mixed and then back to relative

Excel for Beginners By Abishek Mittal

15

Remove data formatting

Surprise! Someone else has used your worksheet, filled in some data, and made the number in cell C6 bold and red to highlight that ABC made the highest sale.

Click in the cell, and then on the Home tab, in the Editing group, click the arrow on Clear Click Clear Formats, which removes the format from the cell. Or you can click Clear All to remove both the data and the formatting at the same time.

Excel for Beginners By Abishek Mittal

16

Using References in Formulas & Functions - Relative Referencing

Excel for Beginners By Abishek Mittal

17

Using References in Formulas & Functions - Absolute Referencing

Excel for Beginners By Abishek Mittal

18

Using References in Formulas & Functions - Mixed Referencing

Excel for Beginners By Abishek Mittal

19

Common Formula Applications

Function Syntax
Every function has to follow a set of rules, or syntax, which specifies how the function should be written

Excel for Beginners By Abishek Mittal

21

Inserting Functions
A function can be entered directly from the Formula Tab
Select the appropriate function from the list of functions Enter the argument values in the Function Arguments dialog box

Excel for Beginners By Abishek Mittal

22

Inserting Functions
A function can be entered directly from the Formula Tab Filling a series using AutoFill is an effective tool

Excel for Beginners By Abishek Mittal

23

Common Functions
COUNT, COUNTIF used for counting SUM, SUMIF used for summation AVERAGE, AVERAGEIF used for averages COUNTIFS, SUMIFS, AVERAGEIFS used to meet multiple criterias Logical Functions like IF, AND, OR Look up functions like VLOOKUP, HLOOKUP PMT function to determine Monthly Loan Payment
Excel for Beginners By Abishek Mittal 24

Logical Functions
A logical function is a function that works with values that are either true or false The IF function is a logical function that returns one value if the statement is true and returns a different value if the statement is false IF(logical_test, value_if_true, [value_if_false])

Excel for Beginners By Abishek Mittal

25

Logical Functions
=IF(A1="YES", "DONE", "RESTART") =IF(A1="MAXIMUM", MAX(B1:B10), MIN(B1:B10)) =IF(D33>0, $K$10, 0)

Nested Ifs A nested IF function is when one IF function is placed

inside another IF function to test an additional condition =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))
Excel for Beginners By Abishek Mittal 26

Logical Functions
The OR function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if all the logical conditions are false

=IF(OR([Years Service]<1,[Annual Salary]>100000),0, IF([Pay Grade]=1,$T$1,IF([Pay Grade]=2,$T$2, IF([Pay Grade]=3,$T$3,"Invalid pay grade"))))

Excel for Beginners By Abishek Mittal

27

Using LookUp Tables & Functions

A lookup table is a table that organizes data you want to retrieve into different categories The categories for the lookup table, called compare values, are located in the tables first column or row To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Excel for Beginners By Abishek Mittal 28

Using LookUp Tables & Functions

A logical function is a function that works with values that are either true or false

Excel for Beginners By Abishek Mittal

29

Data Tools
Sorting Data You can rearrange, or sort, the records in a table or range based on the data in one or more fields The fields you use to order the data are called sort fields You can sort data in ascending or descending order Filtering Data Criteria filters enable you to specify various conditions in addition to those that are based on an equals criterion

Excel for Beginners By Abishek Mittal

30

Pivot Tables and Pivot Charts

Analyzing Data with Pivot Table

A PivotTable is an interactive table that enables you to group and summarize either a range of data or an Excel table into a concise, tabular format for easier reporting and analysis

Excel for Beginners By Abishek Mittal

32

Adding a Report Filter to PivotTable

A report filter allows you to filter the PivotTable to display summarized data for one or more field items or all field items in the Report Filter area

Excel for Beginners By Abishek Mittal

33

Grouping & Refreshing a PivotTable

When a field contains numbers, dates, or times, you can combine items in the rows of a PivotTable and combine them into groups automatically You cannot change the data directly in the PivotTable. Instead, you must edit the Excel table, and then refresh, or update, the PivotTable to reflect the current state of the art objects list

Excel for Beginners By Abishek Mittal

34

Creating a PivotChart
A PivotChart is a graphical representation of the data in a PivotTable A PivotChart allows you to interactively add, remove, filter, and refresh data fields in the PivotChart similar to working with a PivotTable

Excel for Beginners By Abishek Mittal

35

Graphs & Statistical Tools

Creating Charts
A chart, or graph, is a visual representation of a set of data In the Charts group on the Insert tab, click a chart type, and then click a chart subtype in the Chart gallery The data source is the range that contains the data you want to display in the chart

Excel for Beginners By Abishek Mittal

37

Selecting a Chart Type

Excel for Beginners By Abishek Mittal

38

Setting the Pie Slice Colors

In pie charts with legends, its best to make the slice colors as distinct as possible to avoid confusion Click the pie to select the entire data series, and then click the slice you wish to change Change the fill color

Excel for Beginners By Abishek Mittal

39

Creating the Column Chart

A column chart displays values in different categories as columns; the height of each column is based on its value The bar chart is a column chart turned on its side, so each bar length is based on its value

Excel for Beginners By Abishek Mittal

40

Formatting Column Chart Elements

Click the Chart Tools Layout tab on the Ribbon

Excel for Beginners By Abishek Mittal

41

Formatting Column Chart Axes

Click the Chart Tools Layout tab on the Ribbon

Excel for Beginners By Abishek Mittal

42

Formatting Column Chart Elements

Click the Chart Tools Layout tab on the Ribbon

Excel for Beginners By Abishek Mittal

43

Formatting Chart Columns

Click any column in the Sector Weightings chart In the Current Selection group on the Chart Tools Layout tab, click Format Selection

Excel for Beginners By Abishek Mittal

44

Introduction to Macros

Macros
A macro is a series of stored commands that can be run whenever you need to perform the task The Developer tab will enable you to create and use macros If the Developer tab isnt available, you will need to enable it using the Excel Options To protect against Macro Viruses, the macro security settings control what Excel will do about macros in a workbook when you open that workbook

Excel for Beginners By Abishek Mittal

46

Recording a Macro
In the Code group on the Developer tab, click the Record Macro button Enter a name for the macro, and specify the location to store the macro Specify a shortcut key (optional) Enter a description of the macro (optional) Click the OK button to start the macro recorder Perform the tasks you want to automate Click the Stop Recording button

Excel for Beginners By Abishek Mittal

47

Running a Macro
Press the shortcut key assigned to the macro or In the Code group on the Developer tab, click the Macros button Select the macro from the list of macros, and then click the Run button

Excel for Beginners By Abishek Mittal

48

Editing a Macro
In the Code group on the Developer tab, click the Macros button, select the macro in the Macro name list, and then click the Edit button Use the Visual Basic Editor to edit the macro code Click File on the menu bar, and then click Close and Return to Microsoft Excel

Excel for Beginners By Abishek Mittal

49

Creating a Macro Button

In the Controls group on the Developer tab, click the Insert button In the Assign Macro dialog box, select the macro you want to assign to the button

Excel for Beginners By Abishek Mittal

50

Saving Workbook with Macros

On the Quick Access Toolbar, click the Save button Click No in the dialog box Save as an Excel Macro-Enabled Workbook

Excel for Beginners By Abishek Mittal

51

Opening Workbook with Macros

Excel for Beginners By Abishek Mittal

52

Questions ?

Thank You !