Microsoft Excel
Microsoft Excel
Learning Objectives Covered:
LO 06.04 – Demonstrate how to create tables and graphs in Microsoft Excel including the use of formulas
Career Relevancy
Segment of a slide ruleIf you’ve seen the movie Hidden Figures, you may have noticed one of the manual calculating tools used in the movie was a slide rule (a segment is pictured here). Luckily, we do not have to use slide rules for our calculations today. For quick calculations, we can grab the calculator programmed into our phone. For more complex calculations and calculations that have to be done repetitively, we can program our spreadsheet to do the work for us using formulas. The use of formulas in a spreadsheet improve work effectiveness and efficiency in these ways:
Multiple similar calculations can be done quickly without error
The spreadsheet with formulas can be easily used by different people with consistent results
The spreadsheet formulas can be used over and over without having to do the manual calculations again simply because one or more pieces of input have changed
Tables and graphs are ways to display data so it makes sense to us. You have used tables and graphs most of your life—they have become increasingly common in media and on social media as well as in the professional world.
Background
Tables
You have already created tables if you have completed the Week 4 Assignment. A table is nothing more than data organized in rows and columns. Rows go down, columns go across. The individual spots where data is held are called cells.
To make a table more useful, we can add labels. Let’s use a simple example from the business world. You are the supervisor of a small sales team. You need to track the weekly sales of your team members, Ash, Kourtney, Sherra, and Tamika. Here is the raw data:
Ash sold $1,050 in week 1, $1,125 in week 2, and $976 in week 3
Kourtney sold $1,245 in week 1, $1,175 in week 2, and $1,480 in week 3
Sherra sold $1,090 in week 1, $890 in week 2, and $1,115 in week 3
Tamika sold $1,205 in week 1, $1,225 in week 2, and $1,330 in week 3
That’s a pretty confused way to look at it, so let’s put together a table listing the weeks across the top and the sales people on the left side.
Ash Kourtney Sherra Tamika
Week 1
Week 2
Week 3
This is not a pretty table, but it works. We can do the same thing in Excel. It is easier to make the table work for us in Excel than on the course page. Here is the basic table in Excel.
The basic table in Excel
Now, let’s add in the sales numbers.
Table with basic sales data
This is pretty easy to read compared to what we had before, but we want it to look a little clearer. We can apply some simple formatting—like you did in the Word assignments. More importantly, we can set up these cells to display the numbers as currency. Because we have a few other pieces we’ll add when we put in the formulas, we’ll set more than just our active cells to show numbers as currency. Start by selecting cell B2 and then, while keeping the mouse or right click active, select through cell G5. (If you struggle to make the full selection, you can also do it one cell at a time or by a smaller selection.) Your selection will look something like this:
Selection of money cells
Formulas
Your table should now look good and it is readable, but it would be nice to know what we are selling per week and how much each salesperson has sold. It would also be good to know our grand total. We can find out all of this by adding in new labels and then putting in some formulas. First, the labels. We’re going to add a label for weekly totals, one for salesperson totals, and one for the grand total.
We can make this much clearer by drawing in thicker lines or changing the color of the cells. Experiment a bit with the look. (If you’re stuck on how to do that, a simple Google search will give multiple ideas with instructions.)
A note about formulas, you do need to get the format correct for the formulas, but there are different ways to do them as well. For this task, we are going to go with the most basic way to help explain it, but we will show a simple way to make life easier.
First, go to the cell you want to put the formula in. We’ll start with cell A1, then click on Formulas from your menu tabs. As you will see, there are several classifications of formulas available to you. Explore the formulas by clicking on the financial drop-down menu and hover over the formulas to view a description. The description provides details for when to use the formula.
Excel image – Financial
Graphs
Once your data set is complete, you can create a chart, otherwise known as a graph, to provide a visual representation of the data. The chart tool allows you to create a visual presentation of the data in your table. You can select a format that fits your goal and data set. Your options are a Area, Bar, Column, Waterfall, Pie, Line, Scatter, Surface, or Radar charts. With Excel, it is easy to create a chart by simply selecting your data set and then click Insert, then the type of chart you prefer.
Chart image
Prompt
For this assessment, we’re going to create an Excel spreadsheet making a useful table, create a graph from it, and use formulas to help understand the data better.
Assessment instructions:
Download data set by clicking herePreview the document
Step 1: Add employee name data and column and row names
Add the employee name information from the data set to a blank excel file. Be sure to properly name your columns and rows for each category.
Step 2: Create additional employee data and arrange names alphabetically
Add five additional employees to the “Employee” column. You will now need to arrange your employees in alphabetical order. Select all the names in your “Employee” column and then click on the data tab. Locate the “Sort & Filter” group and click on the sort A to Z icon. This will arrange all your names alphabetically and you are ready to move on to the next step.
Step 3: Add remaining data into coordinating fields
Now that you’ve arranged your employees in alphabetical order you can add the absence information for each employee to the correlating month.
Step 4: Add a formula
Create a new column titled “Total days missed” at the end of the data set. Click on cell N2, then click the formulas tab. In the formulas tab click Autosum, then click Sum. The row data should now be encased in a green colored box. Hit the enter key. Click back into cell N2 and drag the green square down to N18. The sums will now be calculated for each employee.
Take a minute to review the days missed for each employee and think about how this data could be useful.
Step 5: Format as a table
Use the short key code of CRTL + A to select the data set. Click Format as table and select a table format. Next, click the My table has headers.
Step 6: Insert a Graph
Select your table, then click the Insert tab. Under the insert tab select Column or Bar chart, then 2D Stacked column. The chart will appear on the screen. In order to make it easier to view the data set and chart you will need to move the chart below the table by right-clicking on the chart and dragging to the desired location.
Step 7: Save file
Save your file as an excel file using App110_lastname_week4_assessment
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
