Introduction to Excel

Microsoft Excel is a powerful spreadsheet application widely used for data analysis, organization, and
calculation. It is part of the Microsoft Office suite and allows users to input, manipulate, and visualize
data efficiently.

Key Features of Excel:

 Worksheets and Cells: Excel uses a grid of rows and columns to organize data into cells.
Each cell can contain text, numbers, formulas, or functions.

 Formulas and Functions: You can perform calculations using formulas (e.g., =A1+B1) and
built-in functions (e.g., =SUM(A1:A10) to sum a range of cells).

 Charts and Graphs: Excel can generate a variety of charts and graphs (e.g., line, bar, pie) to
visually represent data.

 Data Filtering and Sorting: You can sort data in ascending or descending order and filter it
based on certain criteria to make it easier to analyze.

 Pivot Tables: A powerful tool to summarize large datasets and quickly find patterns or
insights by dragging and dropping fields.

 Conditional Formatting: Allows you to highlight data that meets certain conditions, such as
turning cells red if they contain values above a certain threshold.

 Data Validation: Ensures that only certain types of data can be entered into specific cells,
which helps to prevent errors.

 Cell Referencing: Excel uses different types of cell references, such as relative (A1), absolute
($A$1), and mixed (A$1 or $A1) references to control how formulas are copied or moved across
cells.

 Excel Tables: Organize data in structured tables that allow for easy filtering, sorting, and
analysis.

 Collaboration: Excel supports sharing and collaborating on spreadsheets in real-time with

others via cloud services like OneDrive or SharePoint.
Basic Excel Formulas and Functions

Function Work Formula Example

1 Sum The SUM function adds up a =SUM(A1:A10)
range of numbers. This formula will add all the
numbers in cells A1 to A10.

2. AVERAGE The AVERAGE function Formula Example:

calculates the average (mean) =AVERAGE(A1:A10)
of a group of numbers. This will calculate the
average of the numbers in
cells A1 to A10.

3. COUNT The COUNT function counts Formula Example:

the number of cells that =COUNT(A1:A10)
contain numbers within a This counts how many cells in
range. the range A1 to A10 contain
numbers.

4. IF The IF function checks if a =IF(A1 > 50, "Pass",

condition is true or false, and "Fail")
then returns different results This formula checks if the value
depending on the outcome. in cell A1 is greater than 50. If
true, it returns "Pass"; if false, it
returns "Fail"
5 . MIN The MIN function finds the =MIN(A1:A10)
smallest value in a range. This will return the smallest
value in the range A1 to A10.
6 . MAX The MAX function finds the =MAX(A1:A10)
largest value in a range. This will return the largest value
in the range A1 to A10.
7 . VLOOKUP The VLOOKUP function searches =VLOOKUP(B2, A1:C10, 3,
for a value in the first column of FALSE)
a range and returns a value in This looks for the value in B2
the same row from another within the first column
column. (A1:A10) and returns the
value from the third column
(C1:C10) of the same row.
The FALSE means the
 function looks for an exact
match.

8. HLOOKUP The HLOOKUP function is =HLOOKUP(B2, A1:F5, 3,

FALSE)
similar to VLOOKUP but
searches for a value in the This looks for the value in B2
first row of a range. within the first row (A1:F1)
and returns the value from the
third row (A3:F3) of the same
column.

Text functions in excel

Text functions in Excel allow you to manipulate and work with text strings. Here are some of the most
commonly used text functions in Excel:

Function Work Formula Example

1. CONCATENATE The CONCATENATE function =CONCATENATE(A1, " ",
B1)
(or CONCAT in newer Excel
versions) joins two or more This joins the values in cells
text strings together. A1 and B1, with a space in
between.

Note: CONCATENATE is
replaced by CONCAT in newer
versions, but they work
similarly.

2. TEXT The TEXT function allows you to =TEXT(A1, "mm/dd/yyyy")

format a number or date as This formats the date in cell
text, using a specific format. A1 as "month/day/year" (e.g.,
"02/25/2025").

3. LEN The LEN function returns the =LEN(A1)

number of characters in a text This returns the number of
string, including spaces. characters in cell A1.
4. LEFT The LEFT function extracts a =LEFT(A1, 5)
specified number of characters This extracts the first 5
from the beginning (left side) of
a text string.
 characters from the text in
cell A1.

5. RIGHT The RIGHT function extracts a =RIGHT(A1, 3)

specified number of This extracts the last 3
characters from the end (right characters from the text in cell
side) of a text string. A1.

6. MID The MID function extracts a =MID(A1, 3, 5)

substring from a text string, This extracts 5 characters
starting at any position and starting from the 3rd
returning a specified number character in cell A1.
of characters.

7. TRIM The TRIM function removes =TRIM(A1)

all leading and trailing spaces This removes extra spaces
from a text string and reduces from the text in cell A1.
extra spaces between words
to a single space.

8. REPLACE The REPLACE function =REPLACE(A1, 1, 3,

"New")
replaces part of a text string
with another text string, based This replaces the first 3
on a given position. characters in the text in cell
A1 with the word "New".

9. SUBSTITUTE The SUBSTITUTE function =SUBSTITUTE(A1, "old",

"new")
replaces occurrences of a
This replaces the word "old"
specified old substring with a
with "new" in the text in cell A1.
new one.

10. EXACT The EXACT function compares =EXACT(A1, B1)

two text strings and returns This compares the text in
TRUE if they are exactly the cells A1 and B1 and returns
same (case-sensitive) and TRUE if they are exactly the
FALSE if they are not. same, and FALSE otherwise.
Data Manipulation
Data manipulation in Excel refers to the process of organizing, cleaning, transforming, and analyzing
data to gain insights or make it more usable for decision-making.
Excel provides various tools and techniques to manipulate data
effectively. Here are some common data manipulation
techniques in Excel:

Organizing: Sorting, filtering, and grouping data.

Sorting

Sorting data helps to organize information in a specific order

(ascending, descending, or custom).

 How to Sort:
o Select the range of cells you want to sort.
o Go to the Data tab and click Sort.
o Choose to sort by a specific column, and
decide if you want to sort it in ascending or
descending order.
o You can also add multiple sorting levels (e.g., sort by "Name" and then by
"Date").

Filtering

Filtering is a process of sorting data by a certain criteria. It’s an effective way to identify
subsets of data from the larger dataset.

 How to Filter:
o Select the range of cells (or the header row).
o Go to the Data tab and click Filter (or use the keyboard shortcut Ctrl + Shift
+ L).
o Click the drop-down arrows in the column headers and choose filter options like
"Number Filters," "Text Filters," or "Date Filters."

Example: If you only want to show records where the "Sales" column is greater than
$500, you can set a filter for that column.
Grouping

Grouping is an excellent way to analyze your data. Grouping is when you organize data
into smaller sets. You can use this technique to make it easier to analyze the
relationships in your data like quantifying averages, totals, and percentages.

How to Group Data:

 Select the rows or columns you want to group.

 Go to the Data tab and click Group.
 You can group by rows or columns. Excel will create a collapsible group to organize your
data.

Conditional Formatting
Conditional formatting helps you visually highlight important data points based on specific
conditions.

 How to Apply Conditional Formatting:

o Select the data range.
o Go to the Home tab and click Conditional Formatting.
o Choose from options like "Color Scales," "Data Bars," or "Icon Sets."
o You can also create custom rules based on formulas (e.g., highlight cells that are
greater than a certain value).

Example: Highlight all cells with sales greater than $1000 using a green color.

Conditional formatting helps you visually highlight important data points based on specific
conditions.

 How to Apply Conditional Formatting:

o Select the data range.
o Go to the Home tab and click Conditional Formatting.
o Choose from options like "Color Scales," "Data Bars," or "Icon Sets."
o You can also create custom rules based on formulas (e.g., highlight cells that are greater
than a certain value).

Example: Highlight all cells with sales greater than $1000 using a green color.

Removing Duplicates
Excel allows you to remove duplicate entries from a range or table.

 How to Remove Duplicates:

o Select the data range (or the entire table).
 o Go to the Data tab and click Remove Duplicates.
o Select the columns where duplicates should be removed (you can select one or multiple
columns).
o Click OK, and Excel will remove duplicate rows based on your criteria.

Using Formulas for Data Transformation

You can use formulas to transform data dynamically based on specific conditions or calculations.

 Commonly Used Formulas:

o LEFT, RIGHT, MID: Extract specific parts of text.
o TEXT: Format numbers or dates as text.
o CONCATENATE or CONCAT: Combine text from multiple cells.
o SUBSTITUTE: Replace part of a string with new text.
o UPPER, LOWER, PROPER: Change text case.
o IF: Perform logical tests and return different values based on conditions.
o SUM, AVERAGE, COUNT: Perform mathematical operations on numeric data.

Example: =IF(A2 > 100, "High", "Low") – This will return "High" if the value in A2
is greater than 100, or "Low" otherwise.

Using Formulas for Data Transformation

You can use formulas to transform data dynamically based on specific conditions or calculations.

 Commonly Used Formulas:

o LEFT, RIGHT, MID: Extract specific parts of text.
o TEXT: Format numbers or dates as text.
o CONCATENATE or CONCAT: Combine text from multiple cells.
o SUBSTITUTE: Replace part of a string with new text.
o UPPER, LOWER, PROPER: Change text case.
o IF: Perform logical tests and return different values based on conditions.
o SUM, AVERAGE, COUNT: Perform mathematical operations on numeric data.

Example: =IF(A2 > 100, "High", "Low") – This will return "High" if the value in A2
is greater than 100, or "Low" otherwise.

Data manipulation can be a time-consuming and complicated task. But the right
technique can help you save a lot of time and avoid making mistakes. The basic
techniques can help you navigate data manipulation in Excel.

Remember to double-check your data before you manipulate it, too!

Data Visualization
Data Visualization in Excel helps to represent data graphically, making it easier to understand.
Excel offers various chart types like Column, Bar, Pie, Line, Area, Scatter, and Surface charts.

Steps for Data Visualization in Excel:

1. Open the Excel spreadsheet and enter/select the data you want to visualize.
2. Go to the Insert tab and choose a chart from the available options.
3. Alternatively, select a cell in the data and press F11 to create a default chart.

When excel data contains multiple columns and if you want to make a chart
for only a few columns, then select the columns required for making the chart
and press the ‘F11’ function key or click on the Insert tab and select the
chart from the list of charts available.
Data Formatting in Excel
In Excel, formatting tools help improve the appearance of data without changing its content.
These tools make the data more readable and visually appealing.

Steps to Apply Formatting:

1. Select the range of cells where formatting is required.

2. Choose the appropriate formatting tool from the ribbon.
3. Observe the changes on the sheet.

Font: It changes the writing style of the data

 Font Size: It changes the size of the data, can make it appear big or short.


 Increase/Decrease Font Size: It does the same work as by Font Size i.e adjusts
the size of the font accordingly. However, it gets handy as in only one click, the
size is getting changed.
 Bold and Italic: Bold thickens the letter and makes it appear darker than before
while Italic, italicize the letters. Both are useful when some data needs to be
differentiated from the rest.

 Underline: This feature marks a line at the foot of the data.

There is a drop-down button on the right of the underline tool, which has two options:
Underline and Double Underline. Underline is the same as the Underline tool and
Double Underline marks two lines below the text selected.

 Top and Double Bottom Border: This feature creates the border as per selection
from the drop-down button around the selected cells. This is majorly used to
differentiate some cells from the drop-down and create tables in large sheets.

The drop-down in the right of the tool has many options in which we can create the
borders around the cells.
 Fill Colour: As the name suggests, it fills the colour in the cell making it distinct
from the rest. Excel offers a pool of colours from which the choice can be made.

 Font Colour: It changes the colour of the font and makes it appear better. Correct
colour choices can be made from a plethora of options available.
Conditional Formatting

Conditional formatting allows you to apply color schemes and styles to your data cells based on
certain criteria.

 How to Apply Conditional Formatting:

o Select the data range.
o Go to the Home tab → Conditional Formatting.
o Choose a rule, such as "Color Scales," "Data Bars," or "Icon Sets" to visualize how values
compare in the dataset.
o You can also create custom rules based on specific conditions, such as highlighting
values above a threshold.

Sparklines
Sparklines, or in-line charts, are used to display trends and patterns within data directly beside
the data itself. They are typically added at the end of a row or column, providing a compact,
visual representation of data without taking up much space. This makes them ideal for showing
data trends in a concise way alongside the raw values.

Types of Sparklines in Excel

There are three types of sparklines in Excel:
 Line
 Column
 Win-Loss
How to Insert Sparklines in Excel
Follow the below steps to add sparkline in Excel:
Step 1: Go to the Insert tab.
Step 2: Select the cell where you want to insert the sparkline.

>Line” width=”inherit” height=”inherit”>Go

to insert > Sparklines > Select option
Step 3: Select the range of cells for which you have to add a sparkline.

Selecting the range of cells for the sparkline

Step 4: Click on the OK button, the sparkline is now added to the selected
cell.

Sparkline is added to the selected cell

How to change sparklines in Excel

You can also change the Sparkline type – form line to column or vice-versa,
you can do this using the following steps:
Step 1: Click the sparkling you want to change.
Step 2: Click the sparkling Tools Design Tab.
Step 3: In the type group, select the sparkling you want.
Highlighting Data Points in Sparklines
While Sparklines provide a concise view of data trends over time, you can
further enhance their meaning by adding markers an highlighting to
emphasize critical data points.
For instance, you can highlight the maximum and minimum data points, the
first and the last data points, or even negative data points to draw attention to
specific values.
Below is the example showcasing the maximum and minimum data points in
both line and column sparklines:

You can access these options in the sparkline Tools tab, specifically under the
“Show” group.

Pivot Tables
Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract
the significance from a large, detailed data set.
Insert a Pivot Table
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The
default location for a new pivot table is New Worksheet.

3. Click OK.

Drag fields
The PivotTable Fields pane appears. To get the total amount exported of each product,
drag the following fields to the different areas.
1. Product field to the Rows area.

2. Amount field to the Values area.

3. Country field to the Filters area.

Below you can find the pivot table. Bananas are our main export product. That's how
easy pivot tables can be!
Sort a Pivot Table
To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Result:

Filter a Pivot Table

Because we added the Country field to the Filters area, we can filter this pivot table by
Country. For example, which products do we export the most to France?

1. Click the filter drop-down and select France.

Result: Apples are our main export product to France.

Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items. To
change the type of calculation that you want to use, execute the following steps.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.

Result: 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table
If you drag a field to the Rows area and Columns area, you can create a two-
dimensional pivot table. First, insert a pivot table. Next, to get the total amount exported
to each country, of each product, drag the following fields to the different areas.
1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Below you can find the two-dimensional pivot table.

Pivot Chart
To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is
one step too far for you at this stage, but it shows you one of the many other powerful
pivot table features Excel has to offer.

Data Analysis Tools in excel:

Excel is a powerful tool for data analysis, and it offers a variety of features to help you analyze
and visualize data. Here are some of the key data analysis tools in Excel:

What-If Analysis

 What it does: What-If Analysis allows you to model different scenarios based on variable inputs.
It includes tools like Scenario Manager, Data Tables, and Goal Seek to test how changes to
variables affect outcomes.
 How to use: Go to the "Data" tab, click on "What-If Analysis," and choose the desired tool.
Goal Seek

 What it does: Goal Seek helps you find the necessary input value to achieve a specific result in a
formula. It’s useful for "reverse engineering" answers to problems.
 How to use: Go to the "Data" tab, click on "What-If Analysis," and choose "Goal Seek."

Solver

 What it does: Solver is a more advanced version of Goal Seek that can handle multiple variables.
It’s used for optimization problems (e.g., maximizing profits, minimizing costs, or finding the
best allocation of resources).
 How to use: Go to the "Data" tab, click on "Solver," and define the problem.

Data Validation

 What it does: This tool helps ensure the accuracy of data entry by restricting the types of data
that can be entered into a cell (e.g., only numbers, dates, or specific text).
 How to use: Select the cells, go to the "Data" tab, and click "Data Validation."

Basic Excel Marcos :

A macro is a small program or set of actions that you can run repeatedly. Excel macros
are used to automate repetitive tasks to save a lot of time and hassle.

How to record Excel macros:

 1. Enable the Developer Tab
2. Start Recording a Macro
3. Perform Actions to Record
4. Stop Recording

Excel shortcut & Tips:

Excel has a wide range of keyboard shortcuts and tips to help you work more efficiently and
effectively. Here are some of the most useful Excel shortcuts and tips for everyday tasks:

Excel has a wide range of keyboard shortcuts and tips to help you work more efficiently and
effectively. Here are some of the most useful Excel shortcuts and tips for everyday tasks:

Basic Excel Shortcuts

1. Navigating in Excel:
o Move between cells: Use Arrow Keys (Up, Down, Left, Right).
o Jump to the last cell in the row/column: Ctrl + Arrow Key (e.g., Ctrl + Down Arrow to
go to the bottom of a column).
o Go to a specific cell: Ctrl + G (or F5) and enter the cell reference (e.g., A1).
o Move to the next worksheet: Ctrl + Page Down.
o Move to the previous worksheet: Ctrl + Page Up.
2. Selecting Data:
 o Select an entire row: Shift + Spacebar.
o Select an entire column: Ctrl + Spacebar.
o Select all data in the sheet: Ctrl + A (Press Ctrl + A twice to select the entire sheet).
o Extend selection to the edge of data: Ctrl + Shift + Arrow Key.
3. Editing Cells:
o Edit a cell: F2 (or double-click the cell).
o Copy selected cells: Ctrl + C.
o Cut selected cells: Ctrl + X.
o Paste copied/cut cells: Ctrl + V.
o Paste special: Ctrl + Alt + V (to choose from various paste options like values,
formatting, etc.).
o Undo an action: Ctrl + Z.
o Redo an action: Ctrl + Y.
4. Formatting Shortcuts:
o Bold: Ctrl + B.
o Italic: Ctrl + I.
o Underline: Ctrl + U.
o Open Format Cells dialog box: Ctrl + 1.
o Increase font size: Ctrl + Shift + >.
o Decrease font size: Ctrl + Shift + <.
o Format as currency: Ctrl + Shift + $.
o Add border: Ctrl + Shift + 7 (or Alt + H + B).
5. Working with Worksheets:
o Insert a new worksheet: Shift + F11.
o Delete a worksheet: Alt + E + L (after selecting the worksheet).
o Rename a worksheet: Alt + H + O + R.
6. Finding and Replacing:
o Find: Ctrl + F.
o Replace: Ctrl + H.
o Go to the next search result: Enter (after using Find).
7. Save and Print:
o Save workbook: Ctrl + S.
o Print workbook: Ctrl + P.

Advanced Excel Shortcuts

1. Formulas:
o Insert function: Shift + F3.
o AutoSum: Alt + = (automatically sums up selected data).
o Display the Insert Function dialog box: Ctrl + Shift + A.
o Show or hide the formula bar: Ctrl + Shift + U.
o Toggle absolute and relative references in a formula: F4.
2. Navigation in Large Datasets:
o Go to the beginning of the row: Home.
o Go to the beginning of the sheet: Ctrl + Home.
o Go to the end of the sheet: Ctrl + End.
 o Scroll horizontally: Alt + Left Arrow or Alt + Right Arrow.
3. Working with Multiple Workbooks:
o Switch between open workbooks: Ctrl + Tab.
o Close current workbook: Ctrl + W (or Ctrl + F4).
4. Inserting and Deleting:
o Insert a new row: Ctrl + Shift + "+".
o Insert a new column: Ctrl + Space, then Ctrl + Shift + "+".
o Delete a row/column: Ctrl + "-" (after selecting a row or column).
o Insert a new comment: Shift + F2.
5. Advanced Selection:
o Select an entire worksheet: Ctrl + A (press twice to select all).
o Select multiple ranges: Ctrl + Click each range.
o Extend selection by one cell: Shift + Arrow Key.

Excel Tips

1. Use Flash Fill:

o Excel's Flash Fill automatically fills data based on patterns it detects. It's useful for tasks
like splitting names into first and last names, formatting phone numbers, or extracting
initials.
o How to use: Start typing the pattern in a neighboring cell (e.g., type "John" next to "John
Smith"), then press Ctrl + E to auto-fill the rest.
2. Freeze Panes:
o Freeze rows or columns to keep certain data visible while you scroll through large
datasets.
o How to use: Go to View > Freeze Panes and select the appropriate option (e.g., Freeze
Top Row or Freeze First Column).
3. Conditional Formatting:
o Use Conditional Formatting to highlight cells based on specific conditions, like finding
duplicates, highlighting the top or bottom 10%, or applying a color scale.
o How to use: Select the data range, go to Home > Conditional Formatting, and choose a
rule.
4. Use Data Validation for Cleaner Data:
o Data validation helps ensure that only valid data is entered into your spreadsheet (e.g.,
numbers, dates, or predefined lists).
o How to use: Select the cells, go to Data > Data Validation, and define your validation
rules (e.g., only numbers between 1 and 100).
5. AutoSum for Quick Calculations:
o Use AutoSum (or Alt + =) to quickly sum up rows or columns without typing the formula
manually.
o How to use: Select the cell where you want the sum, click AutoSum, and Excel will
automatically select the range to sum.
6. Use the Name Box for Quick Navigation:
o The Name Box (located next to the formula bar) can be used to quickly navigate to
specific cells or named ranges.
 o How to use: Type the cell reference or name of the range in the Name Box and press
Enter to jump to it.
7. PivotTables for Summarizing Data:
o PivotTables are one of the most powerful tools in Excel for summarizing and analyzing
large datasets.
o How to use: Select your data, go to Insert > PivotTable, and drag fields into rows,
columns, values, and filters to analyze your data.
8. Quick Analysis Tool:
o The Quick Analysis Tool allows you to easily apply charts, conditional formatting, totals,
and other analysis features.
o How to use: Select a data range, click on the Quick Analysis button that appears at the
bottom-right, and choose the analysis tool.
9. Text Functions:
o Excel provides powerful text functions to manipulate and clean your data:
 LEFT(), RIGHT(), MID() – Extract parts of a string.
 TRIM() – Remove extra spaces.
 TEXT() – Format numbers or dates as text.
 CONCATENATE() or & – Combine text from multiple cells.
10. Use Excel Templates:

 Excel provides built-in templates for things like budgets, calendars, invoices, and project
management.
 How to use: Go to File > New, and search for the template you need.

These shortcuts and tips can significantly speed up your workflow in Excel, allowing you to
navigate, format, and analyze data more efficiently. Happy Excel-ing!