MS Excel Operations and Calculations

4/26/23, 2:52 PM Ironhack Student Portal
MS Excel: Performing simple operations and

calculations
LESSON
In this lesson, we will learn how to do basic operations in MS Excel and Google
Spreadsheets.
Basic MS Excel Operations

Here we will talk about some simple operations that will help us get started with MS
Excel.
Business Study
You are working as an analyst with a company that distributes goods across the
country. The goods include Office supplies such as binders, desk, and pen sets.
Data is provided as an excel file named excel_sample_data.xlsx . You can

download the dataset from msExcel_visualizations folder on Google drive.
There are several sheets in the excel file. First, we will consider the sheet
SalesOrders .
The excel sheet has the following headers:
Order Date : Date on which the order was made

Region : Sales region
Rep : Name of the representative
Item : Name of the item sold
Units : Number of units sold for the item
Unit Cost : Cost of one item
Total : Total order cost
Your manager wants you to analyze this data and answer some simple business
questions that he has in mind. Open the file and look at the content in the file. You can
https://my.ironhack.com/cohorts/6308c710cd37bd002cee5739/lms/courses/course-v1:IRONHACK+DAPREWORK+MASTER/modules/ironhack-course-chap… 1/13
either use MS Excel if you have it installed and if not, you can use Google
Spreadsheets. Upload the excel file in your google drive, and open it using the File
Menu -> Open .
Navigating the Excel GUI (Graphic User Interface)
This is how the data looks like in MS Excel. In the previous lesson, you looked at the
MS Excel interface. Explore through the different options on the sheet, play around
with the interface and see what different options are provided in MS Excel to
manipulate the data.
Use the shorthand CTRL + Z to undo any changes you made in the data.
Navigating the Google Spreadsheets GUI (Graphic User Interface)
This is how the data looks like in Google Spreadsheets. In the previous lesson, you
looked at the Google Spreadsheet interface. Explore through the different options on
the sheet, play around with the interface and see what different options are provided
in MS Excel to manipulate the data.
Use the shorthand CTRL + Z to undo any changes you made in the data.
Using some simple mathematical functions

Calculating the mean, median, and mode of the three numerical columns present
in our dataset: Units, Units Cost, and Total.
If you would scroll down to rows 46, 47, and 48 you would see the text written for
Mean, median, and mode. In the cells next to the text, we will use formulas to
compute these values.
Please watch the video to see how to use simple mathematical functions in excel:
Simple Mathematical Functions in Excel.
For Google Spreadsheets also, you can calculate these values in the same way.
Here are the steps in MS Excel to calculate the mean (similarly they are applicable in
Google Spreadsheets as well).
Move to the cell E46 and enter =AVER . You would notice that MS excel
automatically provides you with some options for functions that would start with
AVE . You can use UP/DOWN arrows to move through those options.
Move to the option that you want to use and hit “Tab”.
Now select the range of data for which you want to calculate that value.
Hit “Enter/Return” after selecting a range of data.
Here is a screenshot for the same.
Similarly, calculate median and mode for the Units column in the data. This is
how it will look like.
Copy the calculated values for Mean, Median, and Mode from column E and
paste them on columns F and G , in the same three rows.
Check the formulas in the cells where you pasted the content. You will notice that
the formula remains the same, but the range of values in the formula changes.
This is called relative referencing.
Excel remembers the formula and the reference of the range of cells selected
when you copy it. When you paste to a different cell, it uses the same formula but
the new reference.
Check the formulas in F46 , F47 and F48 to understand better.
Check the formulas in G46 , G47 and G48 to understand better.
You can copy a cell or a group of cells that have formulas stored and use in other
cells. Right-click on any cell/cells where you want to paste the copied content.
Excel provides us different options to paste this value. Here is a snapshot to show
those options:
This is what the data looks like (for mean, median, and mode)
To increase or decrease the number of decimal points, use the increase decimal
or decrease decimal option in the Home menu.
Please refer this link to read more about some basic mathematical functions in
excel. Here is a quick mention. You can try and test those functions out on this
data as well.
SUM - Adds its arguments

SUMPRODUCT - The most powerful and useful function in Excel
ROUND - Rounds a number to a specified number of digits
ROUNDUP - Rounds a number up, away from zero
SUBTOTAL - Returns a subtotal of a filtered list or database)
TRUNC - Truncates a number to an integer
INT - Rounds a number down to the nearest integer)
ABS - Returns the absolute value of a number
MOD - Returns the remainder from division
POWER - Returns the result of a number raised to a power

SQRT - Returns a positive square root
Using simple formatting options

You can find multiple formatting options on the Home tab in Excel and the Google
spreadsheets as well.
Snapshots are shown below:
Please refer to the link to read more about Home Tab .
Exercise 1 - Formatting data

This is the current format of the file:
Format the table so that the table looks as shown below:
Note: You might have to change the data format for the columns Unit Cost and
Total from Accounting to Number .
A quick overview of other tabs in excel

Please go through the following links for a better understanding of different tabs in
Excel ribbon:
Insert: resource
Draw: resource
Page Layout: resource
Formulas: resource
Data: resource
Review: resource
View: resource
Developer: resource
Exercise 2 - Creating a filter

Use the data tab to create a filter on the Region column to select the data only from
North Region. Here are the steps:
Go to data tab and click on Filter option:
Filter appears on each column of the data as shown:
Click on filter for region as shown and select only east region:
https://my.ironhack.com/cohorts/6308c710cd37bd002cee5739/lms/courses/course-v1:IRONHACK+DAPREWORK+MASTER/modules/ironhack-course-cha… 10/13
Exercise 3
Performing some simple operations
After filtering the east region, check what are the new mean, median, and
mode.
Also calculate standard deviation and variance for the complete data and east
region.
(You will need to copy the filtered east region data to a new sheet in order to
create updated mean, median, mode, standard deviation and variance functions for
the east region.)
Using simple string functions

Here in this lesson, we will perform some simple string functions/text functions in MS
Excel. The way we use the functions in Google Spreadsheets is the same as in MS
Excel.
Use the provided excel_sample_data.xlsx file. Create a copy of the sheet

SalesOrders , move it to the end, and call it SalesOrderds_StringFunctions .
CONCATENATE function: Used to join/concatenate two strings
=CONCATENATE(C2, " ",B2) : This is a simple implementation of Concatenate

function. We can pass as many arguments as we want. Here we are trying to join
strings from two columns B2 and C2 with another string in between to provide
space between the strings from two columns. Now to use the same formula for
the rest of the rows in the Excel sheet, either you can do CTRL+C on the cell I2
to copy the formula from the cell, and then select the rest of the rows from I3 to
I44 and do CTRL+V to copy the formula on the cells selected. You can also
select the cell I2 , hover to the right bottom corner of the cell till a + or a cross-
hair appear and then drag it all the way down till the cell you want to copy the
formula.
LEFT function: Used to select n number of strings from the left
=LEFT(B2,3) : In the string B2 , select the first three characters from the left of
the string. Copy and paste the formula to the rest of the cells in a similar way as
done for the CONCATENATE function.
RIGHT function: Used to select n number of strings from the right of the string.
=RIGHT(B2,4) : In the string B2 , select the first four characters from the right.
Copy and paste the formula to the rest of the cells in a similar way as done for the
CONCATENATE function.
SUBSTITUTE function: Used to substitute a character or a group of characters

with another character or string in a text.
=LOWER(B2) : In the string B2 , replace/substitute uppercase letter for lowercase

Copy and paste the formula to the rest of the cells in a similar way as done for the
CONCATENATE function.
Exercise 4 - Using simple string functions

In the sheet SalesOrders_StringFunctions the formulas are not copied till
the end. Copy and paste the formula of the first column Concatenate Function
to the end.
In the column Left Function , extract the first two letters of the word from the
column Region .
In the column Right Function , extract the last two letters of the word from the
column Region .
Apply the substitute formula in the rest of the column Substitute Function so
that the words in the column Region are changed to lower case.
Create another column Concatenate Exercise . Concatenate the columns
Left Function and Right Function to create get the complete name of the
region.
Mark as completed
PREVIOUS NEXT
MS Excel and Data Data Visualizations

Visualizations Basics using MS Excel/Google
Spreadsheets

MS Excel Operations and Calculations

Uploaded by

Copyright:

Available Formats

MS Excel Operations and Calculations

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MS Excel Operations and Calculations

Uploaded by

Copyright:

Available Formats

4/26/23, 2:52 PM Ironhack Student Portal

MS Excel: Performing simple operations and

Basic MS Excel Operations

Data is provided as an excel file named excel_sample_data.xlsx . You can

The excel sheet has the following headers:

Order Date : Date on which the order was made

Navigating the Excel GUI (Graphic User Interface)

Navigating the Google Spreadsheets GUI (Graphic User Interface)

Using some simple mathematical functions

Here is a screenshot for the same.

Check the formulas in F46 , F47 and F48 to understand better.

Check the formulas in G46 , G47 and G48 to understand better.

SUM - Adds its arguments

POWER - Returns the result of a number raised to a power

Using simple formatting options

Snapshots are shown below:

Please refer to the link to read more about Home Tab .

Exercise 1 - Formatting data

Format the table so that the table looks as shown below:

A quick overview of other tabs in excel

Exercise 2 - Creating a filter

Go to data tab and click on Filter option:

Filter appears on each column of the data as shown:

Using simple string functions

Use the provided excel_sample_data.xlsx file. Create a copy of the sheet

CONCATENATE function: Used to join/concatenate two strings

=CONCATENATE(C2, " ",B2) : This is a simple implementation of Concatenate

LEFT function: Used to select n number of strings from the left

SUBSTITUTE function: Used to substitute a character or a group of characters

=LOWER(B2) : In the string B2 , replace/substitute uppercase letter for lowercase

Exercise 4 - Using simple string functions

MS Excel and Data Data Visualizations

You might also like