What is a Spreadsheet?
A spreadsheet is an electronic document that is designed for recording, calculating, interpreting the data arranged in rows and columns. A spreadsheet in excel is used by a variety of people like managers, storekeepers, accountants, teachers, students to understand different situations (business profits and losses, salary pays marks) along with planning, modeling, analyzing future and current situations.
How to Create a Spreadsheet on Excel using Different Functions and Formulas?
Creating anything requires 3 things which include Creating a Spreadsheet in Excel too
a)Resources (Microsoft Excel version 2010 or more was preferred, laptop & Charger)
b)Basics (In this article all the basics of Spreadsheet in Excel are discussed in a simple understanding way so be happy no need to worry)
c)Idea and Awareness (Idea means having a knowledge of what you want to create and awareness is how to use excel, which functions & formulas to be used to create a spreadsheet for a situation using excel.
So the only thing required is you should struggle for resource remaining 2 things Basics, Idea & Awareness can be learned from this article itself. So let us start by understanding the basic functions in excel and creating a basic spreadsheet from the functions shared below.
Basic Functions of Spreadsheet in Excel
1) SUM: This basic function of a spreadsheet in excel is used to add 2 or more numbers in the spreadsheet of excel.
Sum Formula: =sum(I11:I16)
a) In this example, if we want to add all the 6 numbers we use =sum() formula where in the brackets we start from 1st number (37512) and drag till (49500) and close the bracket which gives all the values sum which is our total(211544).
b) Similarly, if we want to add only 2 numbers say 35950 and 49500 then we use =sum(j14,j16) so we will get the answer. Hereafter clicking on 1st number we keep, and then select the second number and close the bracket and result displays.
2) COUNT: This basic function of a spreadsheet in excel is used to check how many cells are there in the given range. count function can be used only to numeric values and cannot be used to count cells of id’s with names
Count Formula: =Count(I11:I16)
a) In this example, we will if we want to count the number of cells in the range using the formula we use =count() where in bracket we take the first value (37512) and drag till 49500 (last value) which gives the answer as 6 as there are 6 cells
b) If we try to apply to a column of name answer will be 0 as count can be applied to numeric value only and it cannot count the value which are in names
3) COUNT A: This function of a spreadsheet in excel is similar to count () but an advanced version which can count the number of cells of text, symbols, numeric values in a given range
Count A Formula: =countA()
4) AVERAGE: The average function is the arithmetic mean of the sum of all the numbers in the range, divided by the count of numbers present in the range.
Average Formula: =Average (C18: C24)
a) In this example, if we want to find the average of all the units present we use formula = average() where in the bracket we keep 1st value as 56 and drag the cursor till the end (35) which displays the average of the 7 numbers (38.4287).
b) If we want the average of say 56,20,65 then we use formula =average(56,20,65) which displays average as 47
5) MIN & MAX: These Functions are used to find the lowest and highest value in a range of data
Min Formula : =Min(C26:C34)
Max Formula : =Max(C26:C34)
a) In this example, if we want to find out the lowest and highest value of revenue we use formula =min() for lowest value & =max() for highest value where in the brackets we choose 1st value as 84 and drag the cursor till the end (105) that will display answer of min as 84 and max as 105
Text Functions of Spreadsheet in Excel
1) Trim used to delete unwanted spaces between words
2) Proper used to represent every first word should be capital
3) Upper used to represent every letter in capital
4) Lower used to represent every letter in lower case
5) length used to represent no.of characters in a sentence including spaces
6) Left represents left most characters
7) right represents right most characters
8) Mid used to represent middle characters
9) Concatenate used to merge two words
If Statement in Excel
IF STATEMENT: This statement used to sort the data by the logic of the true or false method.
EG: Using If Statement find out whose salary is good or bad (Hint: Use If Statement logical method to solve this question)
If Statement Formula: =IF(G16>30000,” good”,” poor”)
a) In this example, if we want to know whether the salary earned by the different person was good or not we will use the logical statement that if a person earns more than fixed money it’s good and less represents poor
b)So If statement has
a)logical test: It is the logical method to define whether the value which we want to compare is good or not. For suppose in this example we took 30000 salaries is a base mark where above that are good and below are poor so we use IF(G16>30000) as logic
b)value if true: If the value we were chosen is greater than 30000 as it is done in the above example answer will be displayed as good
c)value if false: If the value is less than 30000 then we get our answer as poor which we can observe for the 2nd and 5th cell of data
Count If & Sum If Functions
Count If: This function helps in counting the only cells of text, numeric and symbols and can calculate the number of cells present in the data for our specified criteria
Count If Formula : =Count If(B2:B16,”NS”)
a) If we want to calculate the specified count of only NS then we will apply formula =countif() where in the brackets we will define
a)range: As we want the value of NS so we select the range of smoking column we start our range from 1st value (NS) to 15th value (NS)
b)criteria: As we want a count for NS so we take criteria as NS and count will be 10 as there are 10 cells that have the value of NS 10 times in the given range.
Sum If: This function is used to add all the data with the same type of ID’S
Sum If Formula: =SUMIF(L5: L16,”ciaz”, N5: N16)
a) In this example, we want to calculate all sales of ciaz cars in different regions with different sales values but similar id ie ciaz so we =sumif() where in the brackets we need to define
a)range: Here we want the value of ciaz which is a car so we go to car column and starting from alto (1st) value to last value we drag till Vitara which will become our range
b)criteria: Now in all the cars we want only the sum value of ciaz cars our criteria is “ciaz”
c)sum range: It is the total range from which we want to get the result of sales of ciaz so we will go to sales column and starting from 2000(1st value) we drag till 2313(last value) then we will get our desired value and procedure is same where if we are doing for west then criteria are a west and select region for range.
Scenario Analysis -Scenario Manager
What will be the disposable income when there is a growth in income and expenditure? Do scenario summary analysis
STEPS TO DO SCENARIO MANAGER :
1) Open excel sheet and write all the names and values given as shown in the figure below
2) The second step is to find out growth income and growth in expenditure :
a) To find out growth income we use formula (=current income(1+percent increase) = 600000(1+20%)
Here 600000 is current income and 20% increases in income
b) To find out growth in expenditure we use formula (=current expenditure(1+percent increase) =200000(1+8%)
Here 200000 is current expenditure and 8% is growth in expenditure
3) Now find out the dispensable income by subtracting expenses from income
720000– 216000 =504000
4) Go to data option in the Spreadsheet and click on what-if analysis – scenario manager
5) Then click on add option and give scenario name, Select changing cells (which includes all the 3 cells – income, expenses, disposable income) and click ok.
6) Then a box named scenario values appears to check the values are correct or not and click ok
- Now add 2 more scenarios (GOOD, BAD)
- Note: Do not change any changing cell. keep the same value which was there when the scenario box is opened and click ok.
- In the scenario values, the values which were displayed for the 1st scenario will be displayed again so we need to change these values according to values which we got before
7)Now click on summary which opens up a box then click on scenario summary and click ok.
NOTE: Result cell will be 1st cell (normal section)of the disposable income
Then a new page opens which displays Scenario summary of 3 situations as shown in the figure below.