BCOR 2020
Basics of Excel
Outline
• Using Microsoft Excel
Basic Spreadsheet Workbook Operations
• Spreadsheet Basics
Cells, References, and Formulas in Excel
Finding the Right Excel Function
Colon Notation
Inserting a Function into a Worksheet Cell
Using Relative versus Absolute Cell
• Install Excel Data Analysis ToolPak for MAC and
Windows
Using Microsoft Excel
Blank Workbook in Excel
Name of the
book
Cell A1 is
selected
Ribbon
Worksheet tab
Groups on the Home Tab in the Ribbon
Home tab
Clipboard
group
Front group
Alignment
group
Number
group
Cell group
Style
group
Editing
group
File Tab, Quick Access toolbar, and Formula bar of an
Excel Workbook
Quick Access
toolbar
Name
Box
Insert Function
button
Formula bar
Formula box
Basic Spreadsheet Workbook Operations
Step 1: Right click on the worksheet tab named sheet.
Step 2: Select the Rename option
Step 3: Enter Nowlin to Rename the worksheet and press ENTER
Continued…
Basic Spreadsheet Workbook Operations
You can create a copy of the newly renamed Nowlin worksheet by
following these steps:
Step 1: Right click the worksheet tab name Nowlin
Step 2: Select the MOVE or COPY…option
Step 3: When the MOVE OR COPY dialog box appears, select the
checkbox for CREATE a copy, and click OK
Continued … Basic Spreadsheet Workbook
Operations
The name of the copied Worksheet will appear as “NOWLIN(2)”. You can
then rename it, if desired, by following the steps outlined previously.
Worksheets can also be moved to other workbooks or to a different
position in the current workbook by using the MOVE or COPY option.
Continued….
Continued… Basic Spreadsheet Workbook Operations
To create additional worksheets follow these steps:
Step 1: Right click on the tab of any existing worksheet
Step 2: Select INSERT
Step 3: When the INSERT dialog box appears, select WORKSHEET
from the General area , and click OK.
OR…New worksheets can also be created using the insert worksheet
button at the bottom of the screen.
Creating, Saving, and Opening Files in Excel
Step 1: Open a blank excel sheet and enter details as shown(sample)
Step 2: Click the File tab on the ribbon
Step 3: Click SAVE in the list of options
Continued..
Continued..Creating, Saving, and Opening Files in Excel
Step 4: Select THIS PC under SAVE AS, and click BROWSE
Step 5: When the SAVE AS dialog box appears:
Select the location where you want to save the file
Enter the filename NOWLIN in the FILENAME: box
Click SAVE
Spreadsheet Basics:
Cells, References, and Formulas in Excel
From the previous excel,
We add Model into Cell A10 and press Bold button.
In A11 cell, we enter the label Quantity
Continued...
Continued…Cells, References, and Formulas in Excel
We enter the labels Total Cost to Produce, Total Cost to Outsource, and
Savings due to Outsourcing in A13,A15, and A17 respectively.
Now we enter formulas in cells B13,B15 and B17.
Continued...
Continued….Cells, References, and Formulas in Excel
The formula for Total Cost to Produce is ( = B4+B11*B5 )
i.e Cell B4 PLUS Cell B11 MUTLIPLIED with Cell B5 and write this entire
formula at Cell B13 ($254,000.00)
NOTE : YOU MUST USE EQUAL TO ( = )SIGN BEFORE WRITING
ANY FORUMULA AT RESPECTIVE CELL
Continued…
Continued….Cells, References, and Formulas in Excel
Next,
Total cost to Outsource is the product of the outsourcing cost per
unit (cell B7) and the quantity (cell B11); this is computed by entering the
formula
= B7*B11 at cell B15 ($35,000.00)
Finally, the savings due to outsourcing is computed by subtracting the
cost of outsourcing (Cell B15) from the production cost (Cell B13). Thus, in
cell B17 we enter the formula = B13-B15 ($219,000.00)
Save file AS Nowlin.xlsx
Finding The Right Excel Function
To identify the functions available in excel click the INSERT Function
button
on the FORMULA bar.
This opens the INSERT function dialog box as shown below
Continued...
Continued….Finding The Right Excel Function
The SEARCH for a function box at the top of the dialog box enables us to
type a brief description of what we want to do.
Colon Notation
Colon notation provides an efficient way to convey arrays and
matrices of cells to functions.
For example, the colon notation provides B2:B13 means cell B2
“through” cell B13, namely the array of values stored in the locations
(B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13).
Continued…
Inserting a Function into a Worksheet Cell
Step 1 : Insert the details as shown below in the new excel worksheet. Save
the file as NowlinFinancial.xlsx
Continued...
Continued…Colon Notation
In cell B15
Enter = SUM(B2:B13). The sum function adds up the elements contained in
the function’s argument.
Continued...
Continued…Colon Notation
Consider, for example, the following function = AVERAGE(B2:B13). The
sum function averages all the elements contained in the function’s argument.
Do this in cell B17
Continued...
Using Relative versus Absolute Cell Reference
One of the most powerful abilities of spreadsheet software such as Excel is
the ability to use relative references in formulas.
Use of a relative reference allows the user to enter a formula once into
Excel and then copy and past that formula to other places so that the
formula will update the correct data without having to retype the formula.
Lets practice this case.
Continued...
Continued…Using Relative versus Absolute Cell
Reference
Step 1: Enter the label profit in cell D1 and press the BOLD button in the Font
group of the Home tab.
Step 2: Enter the formula =B2-C2 in cell D2 as shown.
Step 3: Copy the formula from cell D2 by selecting cell D2 and clicking COPY
from the Clipboard group of the HOME tab or DRAG the cursor from cell D2 to
D13.
Continued...
Continued….Using Relative versus Absolute Cell
Reference
Step 4: Select cells D3:D13.
Step 5: Paste the formula from cell D2 by clicking Paste from the Clipboard
group of the HOME tab.
Values in column D can be seen as shown:
Install Data Analysis ToolPak
for MAC and Windows
MAC : Installation
Step 1: Open new excel sheet and in the menu bar we can see TOOLS
option, and click it.
Continued...
Continued… Install Data Analysis ToolPak in Excel
When we click TOOLS, we can observe at the bottom of the drop-down
window an EXCEL ADD-INS. Click it.
Continued…
Continued… Install Data Analysis ToolPak in Excel
When you click on EXCEL ADD-IN you will see a new pop up window as
shown below and here click on Analysis Tool Pak Check-box and next click
on OK button.
Continued…
Continued… Install Data Analysis ToolPak in Excel
After clicking OK button, now go to DATA tab in the excel ribbon then you
can find DATA ANALYSIS at the TOP RIGHT MOST corner as shown:
WINDOWS : Installation
Step 1: Open a new excel sheet and click on File.
Continued…
Continued….Install Data Analysis ToolPak in Excel
Step 2: Click on Options
Continued…
Continued..Install Data Analysis ToolPak in Excel
Step 3: After clicking options, a new pop up window appears, in that
choose ADD-INS and at the bottom of the window choose EXCEL ADDINS under manage dropdown field and Click on GO button.
Continued…
Continued…Install Data Analysis ToolPak in Excel
Step 4: Next, we can see a ADD-INS pop up window that has four
check boxes. Now click on Analysis Tool pak checkbox and click on OK
button.
Continued…
Continued…Install Data Analysis ToolPak in Excel
Step 5: Now , under menu bar go to DATA and you can find the DATA
ANALYSIS at the right most corner of excel sheet.
Lab Submission
• Zip both files
– Nowlin.xlsx
– NowlinFinancial.xlsx.
• Submit the zip file to canvas.
• Late submission is ½ credit up to 3 days late.
37
Outline
* Using Microsoft Excel
Basic Spreadsheet Workbook Operations
* Spreadsheet Basics
Cells, References, and Formulas in Excel
Finding the Right Excel Function
Colon Notation
Inserting a Function into a Worksheet Cell
Using Relative versus Absolute Cell
* Install Data Analysis ToolPak in Excel in MAC and in
Windows
END
39
Purchase answer to see full
attachment