Formulas and Functions in Spreadsheets
INTRODUCTION
A spreadsheet is a computer application for organization, analysis and storage
of data in tabular form. Spreadsheets were developed as computerized analogs of
paper accounting worksheets. The program operates on data entered in cells of a table.
Each cell may contain either numeric or text data, or the results of formulas that
automatically calculate and display a value based on the contents of other cells. Modern
spreadsheet software can have multiple interacting sheets and can display data either
as text and numerals or in graphical form.
One of the most popular spreadsheet applications is developed by Microsoft
named Microsoft Excel. It features calculation, graphing tools, pivot tables, and
a macro programming language called Visual Basic for Applications. It has been a very
widely applied spreadsheet for these platforms, especially since version 5 in 1993, and
it has replaced Lotus 1-2-3 as the industry standard for spreadsheets.
EXCEL FORMULAS AND FUNCTIONS
Formulas and functions are the bread and butter of Excel. They drive almost
everything interesting and useful you will ever do in a spreadsheet. A formula in Excel is
an expression that returns a specific result. In this laboratory activity, introduces the
basic concepts you need to know to be proficient with formulas in Excel.
Activity 1: Using Named Ranges in Formulas
Objective: In this exercise, you will practice naming cells and will use named cells in a
formula.
1. Open Microsoft Excel and create a blank workbook.
2. Create a table and encode the following data as shown below:
(Select the particular cell and use draw borders and merge cell editing function
as needed)
3. Name cell B1 "Quarterly Allowance".
Procedure:
1. Select cell B1.
2. Type "Quarterly_Allowance" in the Cell Address Box and press Enter:
4. Use the Create from Selection command to name cells in the range A3:E9.
Procedure:
1. Select cells A3:E9.
2. On the Formulas tab, in the Defined Names group, click the Create from
Selection command:
3. In the Create Names from Selection dialog box, check Top
row and Left column and click OK:
5. Using only names in your formulas, answer the following questions in
column A of the worksheet.
1. How much was spent on Groceries and House Rent?
2. How much was spent on Groceries in Jan and Mar?
3. What % of Quarterly Allowance did Electric account for from Jan to
Mar?
Procedure to answer the questions:
1. Type "=Jan Clothes+Jan Fun_Stuff" to cell A11.
2. Type "=Jan Groceries+Mar Groceries" to cell A12.
3. Type "=Total Electricity_Bill/Quarterly_Allowance" to cell A13. (reduce to
two decimal places only)
6. Save the Excel file As
“CCL111-18_YourSection_OLA-06_YourLastnameFirstnameMI.xls”.
7. Make sure to follow the filename format to avoid replacing your classmate's files.
8. Post your excel file in the assignment.
I. References:
Wikipedia, https://en.wikipedia.org/wiki/Spreadsheet
Exceljet, https://exceljet.net/excel-formulas-and-functions
II. For additional readings, watching videos and practices:
Click on the following links:
Exceljet, https://exceljet.net/excel-formulas-and-functions
Excel Easy, https://www.excel-easy.com/introduction/formulas-functions.html
Guru99, https://www.guru99.com/introduction-to-microsoft-excel.html
365 Careers, https://www.youtube.com/watch?v=qk6PVi7wZRo
Kevin Stratvert, https://www.youtube.com/watch?v=Jl0Qk63z2ZY
