Chapter 1 – File Management and Excel Basics

Introduction

In this chapter, we will explore several areas of technical aptitude to set a baseline of knowledge for this course. In order to properly analyze data in Microsoft Excel, we must first understand how we will manage our files for the semester, including in-class work, out-of-class practice, and assignments.

The learning objectives for this chapter are:

  • Setup a cloud storage space using IU’s Onedrive licensing to store and manage K160 files
  • Store K160 files in cloud storage and properly understand file names and modified date tags
  • Format and improve design of our spreadsheet
  • Use basic functions for analysis
  • Use Autofill for efficient data management

 

File Storage and Management

  1. Mac & Chromebook users, when using IUanyWare, choose either the IUanyWare Desktop or the STC Desktop – K201:
  2. When you are logged into a computer lab computer go to This PC or My Files and your IU OneDrive will be there as a Network Location:
  3. When you are not in class, Chromebook and Mac users MUST use IUanyWare to virtually connect to a Windows computer.

If you have your own Windows computer, make sure it has Office 365 on it. You can go to iuware.iu.edu to download and install Office 365 for Windows.

  1. If you have your own Windows computer and you already have a personal OneDrive account set up, you can add your IU OneDrive account, too. Try to follow these steps to add your IU OneDrive as a mapped location:
    • On your personal computer, double-click This PC
    • Right-click on your personal OneDrive and select Settings.
    • Go to the Account tab and select the button to Add an Account.
    • Login with your username@iu.edu email address and passphrase.
  1. Do NOT do any of the following:
    • Use Access or Excel in your browser. If you see either Access or Excel open as a tab in your browser, that is not the correct way of using it.
    • Use Access or Excel directly on a Mac. Mac versions are not the same as Windows versions. You need to use the Windows versions of the software.
    • Use Access or Excel through apps on a Chromebook. Again, these are not the same as the Windows versions. You need to use the Windows versions of the software.

Guided Exercises

In Canvas, download the Chapter01_ExcelBasics file. Notice that AutoSave is on.

Cursors Worksheet

The Cursors worksheet contains some information about the three Excel cursors and what they do. But, we will first use this worksheet to practice alignment, font colors, and fill colors.

  1. Merge and Center the text in cell A1 across A1:E1. Make the font color white and the fill color green.
  2. Center the text in A4 across the range A4:E4. Make the font color red and the fill color yellow.

What is the keyboard shortcut for undoing your previous action?

  1. The Select cursor is used to simply choose cells. By clicking in a cell, you select the cell. A cell that is selected will have a green border around it. This also applies when a range of cells is selected.
    • Select G8:I8 and notice the border around the selected range of cells.
  1. If data needs moved, the Move cursor is used.
    • Select cell G12. Place the cursor on the edge of the green border and click and drag the data to move it to a different cell. Use the keyboard shortcut to undo that action.
  2. When a cell is selected, the data in that cell can be filled across a row or down a column. To do this, the Fill handle is used.
    • Select cells G16:G17. Place your cursor in the lower right corner of the selection until the cursor becomes the fill handle. Click and drag down, then release your mouse and notice that values were automatically filled in.
    • Select cells I16:J16 Place your cursor in the lower right corner of the selection until the cursor becomes the fill handle. Click and drag to the right, then release your mouse and notice that values were automatically filled in.
  1. The Fill handle can also be used to fill functions and formulas to other cells so the same calculation is performed.
    • In cell C25, use a function to find the total revenue for all four locations. Then, fill the function across to D25 to find the total expenses.
    • In cell E21, use a formula to find the profit for the East location. Then, fill the function down to cell E24 to find the profit for all four locations.
  2. Although many people use “function” and “formula” interchangeably, function generally refers to a programmed formula that helps you perform mathematical, statistical, and logical operations. A function begins with words or letters that describe what it does. On the other hand, a formula generally refers to a math problem that you tell Excel to perform using cell references, mathematical operations, values that you type, or some combination of these.
  3. In the previous problem, what was the function used?__________________________
  4. In the previous problem, what was the formula used? __________________________

Autofill Worksheet

Try on your own or work with the person next to you! Use the Autofill worksheet to continue practicing alignment, font & fill colors, and filling data and formulas.

  1. Center the text in A1 across the range A1:K1. Make the font bold, 16pt, light blue font with purple fill.
  2. Merge and center the text in A2 across the range A2:K2. Make the font italicized, 12pt, purple font with light green fill.
  3. You need Row 4 to contain the numbers 1 through 10, starting in cell B4 and going across row 4. Instead of typing all those numbers, use autofill to complete this task.
  4. You need Row 6 to contain even numbers from 2 through 20, starting in cell B6 and going across row 6. Instead of typing all those numbers, use autofill to complete this task.
  5. In cell A9, type January. Use autofill to complete a list of all the months in A9:A20.
  6. In cell C9, type 01/15/2021. Fill down to cell C20. Notice that Excel just listed consecutive days, but you want a list of dates that are on the 15th of each month. Click on the Auto Fill Options button. Choose Fill Months.
  7. Cells F8:I20 contain some basic sales data. Column G lists the quantity of each item that was sold; Column H contains the price per item that was sold.
    • Format the prices in H10:H20 as Currency.
    • In cell I10, write a formula to calculate the revenue made by the sale of the items. Fill the formula down to cell I20 to calculate the revenue for each item.
    • In cell I21, use a function to find the total revenue.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

More Practice

Work you should do before our next class

In your IU OneDrive, open the Chapter01_ExcelBasics file. Use the MoneyEarned worksheet to complete the following tasks.

  1. Merge and center the value in A1 across the range A1:J1. Make the font 24pt and bold. Change the font color to white, background 1 and the fill color to dark red.
  2. In cell A2, replace the current text with your first and last names. Center the text in A2 across the range A2:J2. Make the font 18pt with a dark red font color and a black fill color.
  3. Move the content in cells A3:B3 to cells I3:J3. Format the date that is now in cell J3 as a Long Date. Make columns I and J wider so that the content in I3 and J3 is not cut off.
  4. You went to the job fair and now have a part-time job to earn some extra money. Practice your skills to efficiently fill out the table beginning in row 6 using these directions:
    1. You are going to work for the remainder of the semester, and you’ll get a weekly paycheck. Beginning in cell A6 and going down to cell A21, list the numbers 1-16 to represent each paycheck that you will get. Do not type all those numbers, use autofill to do most the work for you.
    2. You’re a little hesitant about managing your time, so you have asked your supervisor to alternate your hours. In cell B6, type a 6 to represent that you’ll only work 6 hours during the first week. In cell B7, type 10 to show that you’ll work 10 hours in week 2. Then, fill this pattern down column B.
      • Excel thinks you want to increase your hours each week by 4. This is not what you want to do, but you don’t have to type in all the values yourself. Remember, after you fill, click on the Autofill Options button and see if there is an option to help you get the data you want. If you do this correctly, you will work 10 hours in the 16th week.
    3. Your hourly rate is $9.00. Enter this value as 9 in column C and use autofill to fill it down the column. Format C6:C21 as Accounting.
    4. In cell D6, enter a formula to calculate how much money you’ll earn on your first paycheck (before taxes are taken out). This is the product of the number of hours worked and the hourly rate. Use autofill to fill the formula down to cell D21 to calculate the weekly pay for all 16 weeks. These values should format as Accounting on their own.
    5. In cell D22, use a function to calculate the total pay for working all 16 weeks. The value in cell D22 should be $1,152.00.
  5. After the 5th week, your boss compliments your hard work and gives you a $1/hour raise! Change your hourly rate starting in cell C11 to $10.00 and fill it down for the rest of the weeks. Your total pay should now be $1,242.00.

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.