SlideShare a Scribd company logo
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.
Excel PowerPoint Presentation and Tutorial
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
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.
CELL AND SHEET PROTECTION
Ad

More Related Content

Similar to Excel PowerPoint Presentation and Tutorial (20)

Ms excel training
Ms excel training   Ms excel training
Ms excel training
Jensen Group
 
Excel Powerpoint class with video
Excel Powerpoint class with videoExcel Powerpoint class with video
Excel Powerpoint class with video
Rich Johnson
 
Excel advanced formulas
Excel advanced formulasExcel advanced formulas
Excel advanced formulas
VikamSathish
 
Guide to excel
Guide to excelGuide to excel
Guide to excel
accgenius2
 
MS Excel 2013
MS Excel 2013MS Excel 2013
MS Excel 2013
Jahnavee Parmar
 
Excel module 1 PowerPoint
Excel module 1  PowerPoint Excel module 1  PowerPoint
Excel module 1 PowerPoint
Wilmington High School
 
Excel useful tips
Excel useful tipsExcel useful tips
Excel useful tips
anmolbansal09
 
Excel useful tips
Excel useful tipsExcel useful tips
Excel useful tips
sujayramshankar
 
Excel tips
Excel tipsExcel tips
Excel tips
Ashish Patel
 
35 Useful Excel Tips
35 Useful Excel Tips35 Useful Excel Tips
35 Useful Excel Tips
Mukunda Adhikari
 
Excel Useful Tips
Excel Useful TipsExcel Useful Tips
Excel Useful Tips
Srinath Maharana
 
OpenCastLabs Excel chapter-3
OpenCastLabs Excel chapter-3OpenCastLabs Excel chapter-3
OpenCastLabs Excel chapter-3
OpenCastLabs Consulting
 
PPT On Microsoft Excel 2007 Full Information.
PPT On Microsoft Excel 2007 Full Information.PPT On Microsoft Excel 2007 Full Information.
PPT On Microsoft Excel 2007 Full Information.
Umesh Kumar
 
22 Excel Basics
22 Excel Basics22 Excel Basics
22 Excel Basics
Buffalo Seminary
 
Microsoft excel beginner
Microsoft excel beginnerMicrosoft excel beginner
Microsoft excel beginner
Sushate Sandal
 
Microsoft excel beginner
Microsoft excel beginnerMicrosoft excel beginner
Microsoft excel beginner
denstar ricardo silalahi
 
Microsoft excel beginner
Microsoft excel beginnerMicrosoft excel beginner
Microsoft excel beginner
Sushate Sandal
 
Excel Tips
Excel TipsExcel Tips
Excel Tips
ashish83mech
 
MODULE 9 EXELLE.pptx
MODULE 9 EXELLE.pptxMODULE 9 EXELLE.pptx
MODULE 9 EXELLE.pptx
kndnewguade
 
04.ms excel
04.ms excel04.ms excel
04.ms excel
RAJ KUMAR
 

Recently uploaded (20)

Bringing data to life - Crime webinar Accessible.pptx
Bringing data to life - Crime webinar Accessible.pptxBringing data to life - Crime webinar Accessible.pptx
Bringing data to life - Crime webinar Accessible.pptx
Office for National Statistics
 
Professional Certificate in Applied AI and Machine Learning
Professional Certificate in Applied AI and Machine LearningProfessional Certificate in Applied AI and Machine Learning
Professional Certificate in Applied AI and Machine Learning
Nafisur Ahmed
 
Introduction to systems thinking tools_Eng.pdf
Introduction to systems thinking tools_Eng.pdfIntroduction to systems thinking tools_Eng.pdf
Introduction to systems thinking tools_Eng.pdf
AbdurahmanAbd
 
Snowflake training | Snowflake online course
Snowflake training | Snowflake online courseSnowflake training | Snowflake online course
Snowflake training | Snowflake online course
Accentfuture
 
2024 Digital Equity Accelerator Report.pdf
2024 Digital Equity Accelerator Report.pdf2024 Digital Equity Accelerator Report.pdf
2024 Digital Equity Accelerator Report.pdf
dominikamizerska1
 
Important JavaScript Concepts Every Developer Must Know
Important JavaScript Concepts Every Developer Must KnowImportant JavaScript Concepts Every Developer Must Know
Important JavaScript Concepts Every Developer Must Know
yashikanigam1
 
Peeling the onion: How to move through multiple discovery and analysis cycles
Peeling the onion: How to move through multiple discovery and analysis cyclesPeeling the onion: How to move through multiple discovery and analysis cycles
Peeling the onion: How to move through multiple discovery and analysis cycles
Process mining Evangelist
 
Hootsuite Social Trends 2025 Report_en.pdf
Hootsuite Social Trends 2025 Report_en.pdfHootsuite Social Trends 2025 Report_en.pdf
Hootsuite Social Trends 2025 Report_en.pdf
lionardoadityabagask
 
Responsible Data Science for Process Miners
Responsible Data Science for Process MinersResponsible Data Science for Process Miners
Responsible Data Science for Process Miners
Process mining Evangelist
 
From Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
From Data to Insight: How News Aggregator APIs Deliver Contextual IntelligenceFrom Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
From Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
Contify
 
lecture_13 tree in mmmmmmmm mmmmmfftro.pptx
lecture_13 tree in mmmmmmmm     mmmmmfftro.pptxlecture_13 tree in mmmmmmmm     mmmmmfftro.pptx
lecture_13 tree in mmmmmmmm mmmmmfftro.pptx
sarajafffri058
 
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptxConcrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
ssuserd1f4a3
 
Ann Naser Nabil- Data Scientist Portfolio.pdf
Ann Naser Nabil- Data Scientist Portfolio.pdfAnn Naser Nabil- Data Scientist Portfolio.pdf
Ann Naser Nabil- Data Scientist Portfolio.pdf
আন্ নাসের নাবিল
 
Storage Devices and the Mechanism of Data Storage in Audio and Visual Form
Storage Devices and the Mechanism of Data Storage in Audio and Visual FormStorage Devices and the Mechanism of Data Storage in Audio and Visual Form
Storage Devices and the Mechanism of Data Storage in Audio and Visual Form
Professional Content Writing's
 
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
Taqyea
 
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
AngelitaVergara1
 
Urban models for professional practice 03
Urban models for professional practice 03Urban models for professional practice 03
Urban models for professional practice 03
DanisseLoiDapdap
 
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjgIntroduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
MichaelTuffourAmirik
 
Time series analysis & forecasting day 2.pptx
Time series analysis & forecasting day 2.pptxTime series analysis & forecasting day 2.pptx
Time series analysis & forecasting day 2.pptx
AsmaaMahmoud89
 
Get Started with FukreyGame Today!......
Get Started with FukreyGame Today!......Get Started with FukreyGame Today!......
Get Started with FukreyGame Today!......
liononline785
 
Professional Certificate in Applied AI and Machine Learning
Professional Certificate in Applied AI and Machine LearningProfessional Certificate in Applied AI and Machine Learning
Professional Certificate in Applied AI and Machine Learning
Nafisur Ahmed
 
Introduction to systems thinking tools_Eng.pdf
Introduction to systems thinking tools_Eng.pdfIntroduction to systems thinking tools_Eng.pdf
Introduction to systems thinking tools_Eng.pdf
AbdurahmanAbd
 
Snowflake training | Snowflake online course
Snowflake training | Snowflake online courseSnowflake training | Snowflake online course
Snowflake training | Snowflake online course
Accentfuture
 
2024 Digital Equity Accelerator Report.pdf
2024 Digital Equity Accelerator Report.pdf2024 Digital Equity Accelerator Report.pdf
2024 Digital Equity Accelerator Report.pdf
dominikamizerska1
 
Important JavaScript Concepts Every Developer Must Know
Important JavaScript Concepts Every Developer Must KnowImportant JavaScript Concepts Every Developer Must Know
Important JavaScript Concepts Every Developer Must Know
yashikanigam1
 
Peeling the onion: How to move through multiple discovery and analysis cycles
Peeling the onion: How to move through multiple discovery and analysis cyclesPeeling the onion: How to move through multiple discovery and analysis cycles
Peeling the onion: How to move through multiple discovery and analysis cycles
Process mining Evangelist
 
Hootsuite Social Trends 2025 Report_en.pdf
Hootsuite Social Trends 2025 Report_en.pdfHootsuite Social Trends 2025 Report_en.pdf
Hootsuite Social Trends 2025 Report_en.pdf
lionardoadityabagask
 
From Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
From Data to Insight: How News Aggregator APIs Deliver Contextual IntelligenceFrom Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
From Data to Insight: How News Aggregator APIs Deliver Contextual Intelligence
Contify
 
lecture_13 tree in mmmmmmmm mmmmmfftro.pptx
lecture_13 tree in mmmmmmmm     mmmmmfftro.pptxlecture_13 tree in mmmmmmmm     mmmmmfftro.pptx
lecture_13 tree in mmmmmmmm mmmmmfftro.pptx
sarajafffri058
 
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptxConcrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
Concrete_Presenbmlkvvbvvvfvbbbfcfftation.pptx
ssuserd1f4a3
 
Storage Devices and the Mechanism of Data Storage in Audio and Visual Form
Storage Devices and the Mechanism of Data Storage in Audio and Visual FormStorage Devices and the Mechanism of Data Storage in Audio and Visual Form
Storage Devices and the Mechanism of Data Storage in Audio and Visual Form
Professional Content Writing's
 
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
最新版澳洲西澳大利亚大学毕业证(UWA毕业证书)原版定制
Taqyea
 
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
2-Cholera-Outbreaks-and-Waterborne-Pathogens-Typhoid-fever (1).pdf
AngelitaVergara1
 
Urban models for professional practice 03
Urban models for professional practice 03Urban models for professional practice 03
Urban models for professional practice 03
DanisseLoiDapdap
 
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjgIntroduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
Introduction to MedDRA hgjuyh mnhvnj mbv hvj jhgjgjgjg
MichaelTuffourAmirik
 
Time series analysis & forecasting day 2.pptx
Time series analysis & forecasting day 2.pptxTime series analysis & forecasting day 2.pptx
Time series analysis & forecasting day 2.pptx
AsmaaMahmoud89
 
Get Started with FukreyGame Today!......
Get Started with FukreyGame Today!......Get Started with FukreyGame Today!......
Get Started with FukreyGame Today!......
liononline785
 
Ad

Excel PowerPoint Presentation and Tutorial

  • 1. WELCOME TO IDEA-ROUTE DATA ANALYTICS SCHOOL MICROSOFT EXCEL CLASS
  • 2. SECTION: FUNDAMENTAL SKILLS OVERVIEW OF MICROSOFT EXCEL ENTERING, EDITING, AND MANAGING DATA FORMATTING AND DATA ANALYSIS CHAPTER PRACTICE
  • 3. 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.
  • 4. 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.
  • 6. 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.
  • 7. 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.
  • 8. 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.
  • 9. 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.
  • 10. 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.
  • 11. 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.
  • 12. 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.”
  • 13. 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.
  • 15. 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.
  • 16. 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.
  • 17. 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.
  • 18. 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.
  • 19. 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.
  • 20. 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
  • 21. 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.
  • 22. 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
  • 23. 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.
  • 24. 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.
  • 25. 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.
  • 26. 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.
  • 27. 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.
  • 28. 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.
  • 29. 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!.
  • 30. 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.
  • 31. 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.
  • 32. 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.
  • 33. 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.
  • 34. 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).
  • 35. 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."
  • 36. 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.
  • 37. •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.
  • 38. 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.
  • 39. 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.
  • 40. 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.
  • 41. 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.
  • 42. 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
  • 43. 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.
  • 44. CELL AND SHEET PROTECTION
  翻译: