Excel Program

Let’s start by understanding, What is a Spreadsheet?  

Please Download this Sample Data Sheet for your exercises.

This very first exercise will give a kickstart to Spreadsheet, and as you go through it, you’ll naturally get comfortable using Spreadsheets.

Exercise 1A

• In column ‘Cumulative Sale’, compute the total sale upto the that day.

• Click here to use this as a hint.

Exercise 1B

• Use a common formula to create the calculator.

• Cope the formula into multiple rows and see what happens. Can you solve the problem?

• Click here to use dollar referencing of blocking cells.

Exercise 1C

• In the top left cell of the table created, put in a formula for 11×1

• Now copy the formula across the table and complete the table

• Use one Formula only to create the whole table. Click here to get a hint.

Wow! You are through the first level. Now, How can you start simply? Let’s start by making a simple Cost Sheet. 

A cost sheet gives you a clear view of

Exercise 2 – Cost Sheet

Challenges:

• Calculate the Amount for each item

• Calculate the total cost of the product

• Calculate the percentage cost for each item

• Click here to make a pie chart of the item-wise cost breakup.

Hey, you are already halfway through. Amazing! Now, let’s dive deeper, either for you personally, for a shop, or for a big corporation. In every case, it is essential to understand where you are spending. This next Challenge will help you create a great Expense sheet for every case.

Exercise 3 – Expense Sheet

Make sure the date column is formatted as a date and not as text (Click here to know how will you check this?)

Challenges

• Calculate the total expenses separately for October and November using the formula =sumif() Click here

• Calculate maximum, minimum and average spend Click here

• Calculate the total number of transactions using =count(). Click here

• Use =countif() to calculate the number of transaction above the average value, and number of transaction below average value

• Make pie chart of expenses

• Make a bar chart of the total amount spent on Y-Axis and date on X-Axis

• Make a line chart of the date-wise cumulative amount spent

Tough Challenge:

Add one more column called ‘Expense category’, and categorise each row of expense as either ‘Food’, ‘Travel’ or ‘Fun’. Now use countif to calculate the total expenses on each of the three categories. Make a pie chart of the same.

Extra Tough Challenge:

Can you use the formula =match() to calculate on which day you cumulatively spent more than Rs.200!

Pat yourself! You have done a great job by reaching here. Let’s continue with finance; with this next, you’ll be able to make future financial plans for yourself or any company. By going through this challenge to calculate Simple and compound interest, you’ll be able to make your linear growth into exponential growth.

Exercise 4 – Interest & EMI

Challenge 4A (Interest)

• Calculate the Simple Interest

• Calculate the Compound Interest.

• Calculate the ratio of Compound Interest to Simple Interest

Challenge 4B (EMI)

Calculate the Interest payable for the month (to be paid on the balance principal of the previous
month).

These formulas will be useful:

• Principal Repayment = EMI – Interest Payment

• Balance Principal = Previous months Balance Principal – current month Principal Repayment

• Compute this for 15 months

• Use a formula to automatically compute in which month is your Loan being fully repaid! (Hint – Match())

• Use the formula = MAX to remove the error from the rows after the loan has been repaid – so that any negative values get replaced by 0!

What!! You have already reached here! The last two levels are to go. This next Challenge may look scary, but it is to make your life easier. Let’s dive into it straight.

Exercise 5 – Lookup table

• Create the table with headers as shown

• Create two other tables with Product and List price, and one with Amount and
Discount offered

Challenges:

• Use =vlookup() to fill the list price in Column E, based on the product entered in column C, and the price list table

• Fill the discount column G, based on the amount in column F and the Discount
terms table

• Calculate the total and average discount given for all the transactions

• Extra Challenge: Can you use =IFERROR() to remove the error in empty rows (see row 5 – error, row 6 – error removed)

Exercise 5 – Lookup table

Challenges:

• Use the formula VLOOKUP to fill the List Price, based on the item entered in Column C by fetching data from the Price List Table.

• Using the same formula, fill the discount column G, based on the amount in column F and the Discount Terms table

• Calculate the total and average discount given for all the transactions

Extra Challenge:

Can you use =IFERROR() to remove the error in empty rows (see row 5 – error, row 6 – error removed)

You are in the endgame now—one step towards becoming an excel expert. Trust me, after this final challenge; you’ll be able to add immense value to yourself and anyone who needs it.

Exercise 6 – Text Manipulation

Challenges

• For Column D : Compute the month number of birthdate using a formula.

• For Column E : Compute the Month name using a formula. Hint INDEX or VLOOKUP

• For Column F : Compute the number of characters of the length of the name. Hint LENGTH

• For Column G : Find the position of the space in the name, e.g. is Amir Khan, the space is in position 5. Hint
FIND

• For Column I and J, use the formulas =LEFT() and =RIGHT() to separate the first name and last name by using the number in Column F and G

• For Column J : Join the month abbreviation with the last name using the formula =concatenate()

EXTRA TOUGH:

• In Row 2 in column K (cell K2), use the formula =today () to write today’s date, and in cell L2 write today’s month number using the formula =month()

• For each row in column K create a formula to write ‘Happy Birthday!’ if today’s date is their birth date

• (In column L write ‘Birthday month!’ if their birthday is in this month!