Excel Main Features and Functions

Conditional Formatting
Conditional formatting can help make patterns and trends in your data more apparent. To
use it, you create rules that determine the format of cells based on their values, such as the
following monthly temperature data with cell colours tied to cell values.

Data Validation
You can use data validation to restrict the type of data or values that users enter into cells.
For example, you might use data validation to calculate the maximum allowed value in a cell
based on a value elsewhere in the workbook. In the following example, the user has typed
abc , which is not an acceptable value in that cell.

Pivot Table
A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a
PivotTable to analyse 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.
 Subtotaling and aggregating numeric data, summarizing data by categories and
subcategories, and creating custom calculations and formulas.
 Expanding and collapsing levels of data to focus your results, and drilling down to
details from the summary data for areas of interest to you.
 Moving rows to columns or columns to rows (or "pivoting") to see different
summaries of the source data.
 Filtering, sorting, grouping, and conditionally formatting the most useful and
interesting subset of data enabling you to focus on just the information you want.
 Presenting concise, attractive, and annotated online or printed reports.

Pivot Charts
Pivot Charts provide graphical representations of the data in their associated PivotTables.
Pivot Charts are also interactive. When you create a PivotChart, the PivotChart Filter Pane
appears. You can use this filter pane to sort and filter the PivotChart's underlying data.
Changes that you make to the layout and data in an associated PivotTable are immediately
reflected in the layout and data in the PivotChart and vice versa.

Pivot Charts display data series, categories, data markers, and axes just as standard charts
do. You can also change the chart type and other options such as the titles, the legend
placement, the data labels, the chart location, and so on.

Difference between Pivot Charts and Standard Charts

Row/Column orientation Unlike a standard chart, you cannot switch the row/column
orientation of a PivotChart by using the Select Data Source dialog box. Instead, you can pivot
the Row and Column labels of the associated PivotTable to achieve the same effect.

Chart types You can change a PivotChart to any chart type except an xy (scatter), stock, or
bubble chart.

Source data Standard charts are linked directly to worksheet cells, while Pivot Charts are
based on their associated PivotTable's data source. Unlike a standard chart, you cannot
change the chart data range in a PivotChart's Select Data Source dialog box.

Formatting Most formatting—including chart elements that you add, layout, and style—is
preserved when you refresh a PivotChart. However, trendlines, data labels, error bars, and
other changes to data sets are not preserved. Standard charts do not lose this formatting
once it is applied.

Although you cannot directly resize the data labels in a PivotChart, you can increase the text
font size to effectively resize the labels.

Slicer

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick
filtering, slicers also indicate the current filtering state, which makes it easy to understand
what exactly is currently displayed.

Timeline

Instead of adjusting filters to show dates, you can use a PivotTable Timeline—a dynamic
filter option that lets you easily filter by date/time, and zoom in on the period you want with
a slider control. Click Analyse > Insert Timeline to add one to your worksheet. Much like
a slicer for filtering data, you can insert a Timeline one time, and then keep it with your
PivotTable to change the range of time whenever you like.

Sparkline

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. Position a sparkline near its
data for greatest impact.

Main Functions in Excel

VLOOKUP

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

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."

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

XLOOKUP

Use the XLOOKUP function to find things in a table or range by row. For example, look up
the price of an automotive part by the part number, or find an employee name based on
their employee ID. With XLOOKUP, you can look in one column for a search term and return
a result from the same row in another column, regardless of which side the return column is
on.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],

[match_mode], [search_mode])

MATCH

The MATCH function searches for a specified item in a range of cells, and then returns the
relative position of that item in the range. For example, if the range A1:A3 contains the
values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because
25 is the second item in the range

=MATCH(lookup_value, lookup_array, [match_type])

INDEX

The INDEX function returns a value or the reference to a value from within a table or range.

There are two ways to use the INDEX function:

 If you want to return the value of a specified cell or array of cells, see Array form.
 If you want to return a reference to specified cells, see Reference form.

Array form
Returns the value of an element in a table or an array, selected by the row and column
number indexes.

Syntax

=INDEX(array, row_num, [column_num])

Reference form

Returns the reference of the cell at the intersection of a particular row and column. If the
reference is made up of non-adjacent selections, you can pick the selection to look in.

Syntax

=INDEX(reference, row_num, [column_num], [area_num])

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 if your comparison is False.

For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).