Excel PowerPoint Presentation and Tutorial

WELCOME TO IDEA-ROUTE
DATA ANALYTICS SCHOOL
MICROSOFT EXCEL CLASS

SECTION: FUNDAMENTAL SKILLS
OVERVIEW OF MICROSOFT EXCEL
ENTERING, EDITING, AND MANAGING DATA
FORMATTING AND DATA ANALYSIS
CHAPTER PRACTICE

LEARNING OBJECTIVES
 Examine the value of using Excel to make decisions.
 Learn how to start Excel.
 Become familiar with the Excel workbook.
 Understand how to navigate worksheets.
 Examine the Excel Ribbon.
 Examine the right-click menu options.
 Learn how to save workbooks.
 Examine the Status Bar.

The Excel Workbook
A workbook is an Excel file
that contains one or more
worksheets (referred to as
spreadsheets). Excel will
assign a file name to the
workbook, such
as Book1, Book2, Book3,
and so on, depending on
how many new workbooks
are opened.

The Excel Ribbon
Excel’s features and commands are found in the Ribbon, which is the upper area of the Excel
screen that contains several tabs running across the top. Each tab provides access to a
different set of Excel commands. Below Example shows the commands available in the
Home tab of the Ribbon. “Command Overview for Each Tab of the Ribbon” provides an
overview of the commands that are found in each tab of the Ribbon.

Tab Name Description of Commands
File
Also known as the Backstage view of the Excel workbook. Contains all commands for opening, closing, saving, and creating
new Excel workbooks. Includes print commands, document properties, e-mailing options, and help features.The default
settings and options are also found in this tab.
Home
Contains the most frequently used Excel commands. Formatting commands are found in this tab along with commands for
cutting, copying, pasting, and for inserting and deleting rows and columns.
Insert Used to insert objects such as charts, pictures, shapes, PivotTables, Internet links, symbols, or text boxes.
Page Layout
Contains commands used to prepare a worksheet for printing.Also includes commands used to show and print the gridlines
on a worksheet.
Formulas Includes commands for adding mathematical functions to a worksheet.Also contains tools for auditing mathematical formulas.
Data
Used when working with external data sources such as Microsoft Access, text files, or the Internet.Also contains sorting
commands and access to scenario tools.
Review
Includes Spelling and Track Changes features.Also contains protection features to password protect worksheets or
workbooks.
View Used to adjust the visual appearance of a workbook. Common commands include the Zoom and Page Layout view.
Draw
Provides drawing options for using a digital pen, mouse or finger depending on the type of device (laptop with touch screen,
tablet, computer, etc).This tab is not visible by default. See below on how to customize the Ribbon to add or remove tabs.

Entering Data
You will begin building the workbook shown by
manually entering data into the worksheet. The
following steps explain how the column headings in
Row 2 are typed into the worksheet:
1. Click cell location A2 on the worksheet.
2. Type the word Month.
3. Press the RIGHT ARROW key. This will enter the
word into cell A2 and activate the next cell to the
right.
4. Type Unit Sales and press the RIGHT ARROW key.
5. Repeat step 4 for the words Average Price and
then again for Sales Dollars.
The picture shows how your worksheet should
appear after you have typed the column headings
into Row 2. Notice that the word Price in cell location
C2 is not visible. This is because the column is too
narrow to fit the entry you typed. We will examine
formatting techniques to correct this problem in the
next section.

Data Entry
It is very important to proofread your worksheet
carefully, especially when you have entered
numbers. Transposing numbers when entering
data manually into a worksheet is a common
error. For example, the number 563 could be
transposed to 536. Such errors can seriously
compromise the integrity of your workbook.

Editing Data
Data that has been entered in a cell can be changed by
double clicking the cell location or using the Formula
Bar. You may have noticed that as you were typing data
into a cell location, the data you typed appeared in the
Formula Bar. The Formula Bar can be used for entering
data into cells as well as for editing data that already
exists in a cell. The following steps provide an example
of entering and then editing data that has been entered
into a cell location:
1.Click cell A15 in the Sheet1 worksheet.
2.Type the abbreviation Tot and press the ENTER key.
3.Click cell A15.
4.Move the mouse pointer up to the Formula Bar. You
will see the pointer turn into a cursor. Move the cursor
to the end of the abbreviation Tot and left click.
5.Type the letters al to complete the word Total.
1. Double click cell A15.
2. Add a space after the word Total and type Sales.
3. Press the ENTER key.

Auto Fill
The Auto Fill feature is a valuable tool when manually
entering data into a worksheet. This feature has many
uses, but it is most beneficial when you are entering
data in a defined sequence, such as the numbers 2, 4,
6, 8, and so on, or nonnumeric data such as the days
of the week or months of the year.
The following steps demonstrate how Auto Fill can be
used to enter the months of the year in Column A:
1. Click cell A3 in the Sheet1 worksheet.
2. Type the word January and press the ENTER key.
3. Click cell A3 again.
4. Move the mouse pointer to the lower right corner of
cell A3.
You will see a small square in this corner of the cell;
this is called the Fill Handle When the mouse pointer
gets close to the Fill Handle, the white block plus sign
will turn into a black plus (+) sign.

Left click and drag the Fill
Handle to cell A14. Notice that
the Auto Fill tip box indicates
what month will be placed into
each cell. Release the mouse
button when the tip box reads
“December.”

1. Click the Auto Fill Options button.
2. Click the Copy Cells option. This
will change the months in the range
A4:A14 to January.
3. Click the Auto Fill Options button
again.
4. Click the Fill Months option to
return the months of the year to
the cell range A4:A14. The Fill Series
option will provide the same result.

CHAPTER PRACTICE
From what we have learn in this section let’s enter the below data in Excel to make
it look exactly like this.
Tools used:
1. Merge and Center align the heading.
2. Use center align tool to center align the subheading.
3. Font field to make changes to the heading and the subheading.
4. Enter the data’s on your worksheet and represent the values for budget and Q1
– Q4 with a dollar sign.

MATHEMATICAL COMPUTATION
WITH MICROSOFT EXCEL
LEARNING OBJECTIVES
 Learn how to create basic formulas.
 Understand relative referencing when copying and pasting formulas.
 Work with complex formulas by controlling the order of mathematical operations.
 Understand formula auditing tools.

The table below shows the completed workbook that
will be demonstrated in this chapter. Notice that this
workbook contains four worksheets. The first
worksheet, Budget Summary, serves as an overview
of the data that was entered and calculated in the
second and third worksheets, Budget
Detail and Loan Payments. The second
worksheet, Budget Detail, provides a detailed list of
all the expenses and the third worksheet, Loan
Payments, provides information regarding car
payment and mortgage payment amounts. The last
worksheet will be use to test our knowledge on
Organizing Data.

CREATING A BASIC FORMULA
Formulas are used to calculate a variety of mathematical outputs in Excel and can be used to create virtually any
custom calculation required for your objective. Furthermore, when constructing a formula in Excel, you use cell
addresses that, when added to a formula, become cell references. This means that Excel uses, or references, the number
entered into the cell location when performing the calculation. As a result, when the numbers in the cells that are
referenced are changed, Excel automatically recalculates the formula and produces a new result. This is what gives
Excel the ability to create a variety of what-if scenarios, which will be explained later in the chapter.

To demonstrate the construction of a basic
formula, we will begin working on the Budget
Detail worksheet, which is shown BELOW. To
complete this worksheet, we will enter some data,
and then create several formulas and
functions. The table provides definitions for each
of the spend categories listed in the range
A3:A11. When you develop a personal budget,
these categories are defined on the basis of how
you spend your money. It is likely that every
person could have different categories or define
the same categories differently. Therefore, it is
important to review the definitions in the other
TABLE BELOW to understand how we are defining
these categories before proceeding.

CATEGORY DEFINITION
Utilities Electricity, heat, water, home phone, cable, Internet access
Cell Phone Cell phone plan and equipment charges
Food Groceries
Gas Cost of gas for vehicle
Clothes Clothes, shoes, and accessories
Insurance Renter, homeowner, and/or car insurance
Entertainment Activities like dining out, movie and theater tickets, parties, and so on
Vacation Vacation expenses
Miscellaneous Any other spending categories

The amount of money spent each month for each category,
as well as the amount of money spent last year, is already
entered into the worksheet. We will write formulas that will
calculate the annual (yearly) amount spent, the percent of
the total spent each category represents, as well as the
percent change from last year’s spending to the current year.
The first formula will calculate the Annual Spend values. The
formula will be constructed so that it takes the values in the
Monthly Spend column and multiplies them by 12 (the
number of months in a year). This will show how much
money will be spent per year for each of the categories listed
in Column A. Since the first category is Utilities, we will start
by creating the formula to multiply the Monthly Spend
amount in B3 by 12. This formula will be created in D3 – the
Annual Spend cell for the Utilities category.
This formula will be written as: =B3*12
Formulas always start with
the equal sign. This signifies
to Excel that the contents of
the cell should be
calculated, not just
displayed as basic text or
numbers.

1. Switch to the Budget Detail worksheet. Click cell D3.
2. Type an equal sign =
When the first character entered into a cell is an equal sign,
signals Excel to perform a calculation.
3. Type B3. This adds B3 to the formula, which is now a cell
reference. Excel will use whatever value is entered into cell B3 in
the calculation.
4. Type the * . This is the symbol for multiplication in Excel. As
shown in Table 2.2 the mathematical operators in Excel are
slightly different from those found on a typical calculator.
5. Type the number 12. This multiplies the value in cell B3 by
12. In this formula, a number, or constant, is used instead of a
cell reference because it will not change. In other words, there
will always be 12 months in a year.
6. Press the ENTER key. This enters the formula into the cell.
Symbol Operation
+ Addition
− Subtraction
/ Division
* Multiplication
^ Power/Exponent

Why?
Use Cell References
Cell references enable Excel to automatically recalculate when one or more inputs in the
referenced cells are changed. Cell references also allow you to trace how results are being
calculated in a formula. You should never use a calculator to determine a mathematical
output and type it into the cell location of a worksheet. Doing so eliminates Excel’s cell-
referencing benefits as well as your ability to trace a formula to determine how results are
being calculated.

The Annual Spend for Utilities is $3,000
because the formula is taking the
Monthly Spend in cell B3 and
multiplying it by 12. If the value in cell
B3 is changed, the formula
automatically produces a new result.

RELATIVE REFERENCES (COPYING AND PASTING FORMULAS)
Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations.
For example, in cell D3 we have calculated the annual spend for the Utilities category, but this
calculation needs to be performed for the rest of the cell locations in Column D. Since we used the B3
cell reference in the formula, Excel automatically adjusts that cell reference when the formula is
copied and pasted into the rest of the cell locations in the column. This is called relative referencing
and is demonstrated as follows:
1. Click cell D3.
2. Place the mouse pointer over the Auto Fill Handle in the bottom right corner of the cell.
3. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag
down to cell D11. This pastes the formula into the range D4:D11.
4. Double click cell D6. Notice that the cell reference in the formula is automatically changed to B6.
5. Press the ENTER key.

The table shows the results added to the rest of the
cell locations in the Annual Spend column. For each
row, the formula takes the value in the Monthly Spend
column and multiplies it by 12. You will also see that
cell D6 has been double clicked to show the formula.
Notice that Excel automatically changed the original
cell reference of B3 to B6. This is the result of relative
referencing, which means Excel automatically adjusts a
cell reference relative to its original location when it is
pasted into new cell locations. In this example, the
formula was pasted into eight cell locations below the
original cell location. As a result, Excel increased the
row number of the original cell reference by a value of
one for each row it was pasted into.

ENTERING EDITING AND MANAGING DATA
LEARNING OBJECTIVES
• Understand how to enter data into a worksheet.
• Examine how to edit data in a worksheet.
• Examine how Auto Fill is used when entering data. Understand how to
delete data from a worksheet and use the Undo command.
• Examine how to adjust column widths and row heights in a worksheet.
• Understand how to hide columns and rows in a worksheet.
• Examine how to insert columns and rows into a worksheet.
• Understand how to delete columns and rows from a worksheet.
• Learn how to move data to different locations in a worksheet.

EXCEL IF FUNCTION
The IF function is one of the most popular functions in Excel, and it allows you to make
logical comparisons between a value and what you expect.
So an IF statement can have two results. The first result is if your comparison is True, the
second IS if your comparison is False.

EXCEL IFERROR FUNCTION
You can use the IFERROR function to trap and handle errors in a formula. IFERROR returns a value you
specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
Syntax
IFERROR (value, value_if_error)
The IFERROR function syntax has the following arguments:
•value Required. The argument that is checked for an error.
•Value_if_error Required. The value to return if the formula evaluates to an error. The following error types
are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

INSERT COMMENTS AND NOTES IN
EXCEL
You can add comments to cells. When a cell has a comment, an indicator appears in the corner of the cell.
When you hover your cursor over the cell, the comment appears.

CHARTS IN EXCEL AND CUSTOMIZATION
Create a chart (graph) that is recommended for your data, almost as fast as using the chart
wizard that is no longer available.
Creating chart in Excel
1.Select the data for which you want to create a chart.
2.Click INSERT > Recommended Charts.
3.On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data,
and click any chart to see how your data will look.
4.If you don’t see a chart you like, click All Charts to see all the available chart types.
5.When you find the chart you like, click it > OK.
6.Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the
chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the
data that is shown in the chart.
7.To access additional design and formatting features, click anywhere in the chart to add the CHART
TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.

ROTATING TEXT IN EXCEL
If you want to change the way data appears in a cell, you can rotate the font angle, or change
the text alignment.
Change the orientation of text in a cell
1.Select a cell, row, column, or a range.
2.Select Home > Orientation , and then select an option.
You can rotate your text up, down, clockwise, or counterclockwise, or align text vertically:
Rotate text to a precise angle
3.Select a cell, row, column, or a range.
2.Select Home > Orientation > Format Cell Alignment.
3.Under Orientation on the right side, in the Degrees box, use the up or down arrow to set the
exact number of degrees that you want to rotate the selected cell text.
Positive numbers rotate the text upward. Negative numbers rotate the text downward.

VLOOKUP FUNCTION
Microsoft Excel is a deceptively powerful tool for data management. It helps users
analyze and interpret data easily. Often not appreciated for the range of tasks it lets the
user perform, Microsoft Excel is undoubtedly a powerful and very popular tool used by
almost every organization, even today.
Excel provides an extensive range of functions that makes it easier to work with data.
VLOOKUP in Excel is one of such function. VLOOKUP works as a search function by
looking for specific data vertically across a table or spreadsheet.
Let’s go ahead and understand what exactly VLOOKUP in Excel is.

WHAT IS VLOOKUP IN EXCEL
VLOOKUP stands for Vertical Lookup. As the name specifies, VLOOKUP is a built-in Excel function that helps
you look for a specified value by searching for it vertically across the sheet. VLOOKUP in Excel may sound
complicated, but you will find out that it is a very easy and useful tool once you try it. Let’s look at some
simple examples.
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an
automotive part by the part number, or find an employee name based on their employee ID.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range
containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

HLOOKUP IN EXCEL
Searches for a value in the top row of a table or an array of values, and then returns a value in the same
column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located
in a row across the top of a table of data, and you want to look down a specified number of rows. Use
VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The H in HLOOKUP stands for "Horizontal."

SYNTAX
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
The HLOOKUP function syntax has the following arguments:
•Lookup_value Required. The value to be found in the first row of the table. Lookup_value can be a value, a
reference, or a text string.
•Table_array Required. A table of information in which data is looked up. Use a reference to a range or a range
name.
• The values in the first row of table_array can be text, numbers, or logical values.
• If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: ...-2, -1,
0, 1, 2,... , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.
• Uppercase and lowercase text are equivalent.
• Sort the values in ascending order, left to right. For more information, see Sort data in a range or table.

•Row_index_num Required. The row number in table_array from which the matching value will be returned.
A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row
value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if
row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
•Range_lookup Optional. A logical value that specifies whether you want HLOOKUP to find an exact match
or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact
match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will
find an exact match. If one is not found, the error value #N/A is returned.

XLOOKUP FUNCTION
The XLOOKUP function is the next generation lookup function in Excel that has the functionality of
both the VLOOKUP function as well as the HLOOKUP function, without the limitations. It performs
either a vertical lookup or horizontal lookup by searching for a value in a row or column of a table
and returning a corresponding value in a table. Unlike VLOOKUP and HLOOKUP, the XLOOKUP
function does not require the lookup value to be in the first column or row of a table, and it can
return a default value when a match is not found instead of the #N/A error.

CONDITIONAL FORMATTING
Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify.
This changes the appearance of a cell range based on a condition (or criteria). You can use conditional
formatting to highlight cells that contain values which meet a certain condition.

DATA VALIDATION
Data validation refers to the process of ensuring the accuracy and quality of data. It is implemented by
building several checks into a system or report to ensure the logical consistency of input and stored
data. In automated systems, data is entered with minimal or no human supervision.
Excel can restrict data entry to certain cells by using data validation, prompt users to enter valid data
when a cell is selected, and display an error message when a user enters invalid data.

PIVOT TABLE
A pivot table is a powerful data summarization tool that can automatically sort, count, and sum up
data stored in tables and display the summarized data.
A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a
PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A
PivotTable is especially designed for: Querying large amounts of data in many user-friendly ways.

PIVOT CHART AND PIVOT TABLE
Pivot Charts help in visualizing Pivot Tables. To create Pivot Charts.
Select any cell from the Pivot Table and choose the Pivot Chart option from insert present in
the Ribbon Tab

SPARKLINES
A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines
to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to
highlight maximum and minimum values.
The Sparkline control supports three different sparkline types, namely Line, Column and Winloss, for
visualizing data in different context. For example, Line charts are suitable to visualize continuous data,
while Column sparklines are used in scenarios where data comparison is involved.

Excel PowerPoint Presentation and Tutorial

Recommended

More Related Content

Similar to Excel PowerPoint Presentation and Tutorial (20)

Recently uploaded (20)

Excel PowerPoint Presentation and Tutorial