Introduction: Creating Tables and Scatter Plots in Excel
For anyone who aspires to be part of the business world, it is necessary to have a basic knowledge of programs like Excel. We will provide easy-to-follow, learner-friendly instructions that will guide the user through the fundamentals of creating tables and scatter plots on Excel. This will include a guide through formatting options and other handy tips. These instructions are fit for users with little to no experience with Excel and completion of the instruction set is estimated at approximately fifteen minutes. It is recommended to print off the instructions because it may be difficult to look at both the instruction set and excel at the same time.
You will need a laptop or desktop computer enabled with the latest version of Windows Excel, internet access, and a computer mouse (this material is optional: many users find Excel easier to navigate with an external computer mouse versus a touch pad mouse on a laptop).
Estimated Time of Completion:
About 15-20 minutes depending on complexity of data
Caution: Users should be careful to save their work in Excel frequently in case of computer crash. This will reduce risk of losing work.
Note on Our Sample Data Set:
The table shown above (Table B-1: wright for height table) is the sample data set that we used when forming our table and scatter plot. We provide it so that users may follow along and use this set as a reference for the instructions. However, users are encouraged to use their own sets of data. (For our purposes, we only use the the first fourteen rows of the two leftmost columns of the table.)
Step 1: Open a Blank File in Windows Excel
1.1 Go to the folder where you want your Excel spreadsheet to be saved.
1.2 Right click anywhere inside that folder.
1.3 Select new in the pop up menu.
1.4 Select the Excel icon.
Note: This is the point where you can name the Excel document. If the name is already highlighted, type in the desired name of the document. If it is not already highlighted, right click the document, select rename in the pop up menu, and type in the desired name of the document.
Step 2: Input Data
Note: Excel spreadsheets are made up of individual, formattable rectangles called cells. These cells make up tables.
2.1 Click the cell that you want to type the data into.
2.2 Type the data into it (see above image).
Note: Data for scatter plots should include exactly one x input (an independent variable) and one y input (a dependent variable). This means your data table should have two columns. Each column should include a title at the top (this will help later when formatting the scatter plot). Also Note: Cells can be automatically resized based on the amount of text inside them by double clicking the right hand border line of the top column label (marked A, B, C, etc.)
Step 3: Format Data Into a Table
3.1 Highlight desired data by dragging your mouse from one top corner of the data to the opposite bottom corner so that all of the desired data is highlighted.
3.2 Select the insert tab on the ribbon. Note: Excel is formatted with a ribbon (a menu strip of tools, formatting options, and adjustable features located just above the spreadsheet). For more information about the ribbon and its uses, go to http://www.excel-easy.com/basics/ribbon.html.
3.3 Click insert table. Note: You now have a formattable table with drop down options above each column of data. These drop down options allow you to organize your data in the numerical order of your choice (ascending, descending, frequency, etc.)
3.4 Click anywhere on the new table and the design tab will appear on the ribbon. Note: You can now choose the desired colors and/or styles or your table. This includes a wide variety of pre-selected and customizable options from Excel.
Step 4: Highlight Table to Form a Scatter Plot
4.1 Drag your mouse from one top corner of the table to the opposite bottom corner so that the entire table is highlighted.
4.2 Select the scatter plot among the chart options (see above image).
Step 5: Format Plot
5.1 Click anywhere on the chart.
5.2 Select Chart Design on the ribbon to move the chart to another sheet (see above image).
Note: You can change the name of the sheet in order to quickly find your scatter plot.
Step 6: Label Axes on Scatterplot
6.1 Select Add Chart Element under Chart Design on the ribbon (see above images).
6.2 Label the horizontal axis with the independent variable.
6.3 Label the vertical axis with the dependent variable.
Step 7: Save the Workbook
7.1 On the top left corner of the screen, select the ribbon tab titled “File”.
7.2 On the left side of the screen, select “Save As”.
7.3 Select your desired folder where you want to save your workbook. If you cannot find your folder, select “Browse” and search the name of the folder.
7.4 In the “File name:” section, type the title of your workbook.
7.5 Select “Save”.
7.6 Exit the workbook.
Step 8: Conclusion
Now that you have completed the basics of an Excel workbook, you will be able to apply these skills to the business world. Your workbook, complete with a data table and corresponding scatterplot, will provide the fundamentals of how to use Excel both professionally and personally. From a company balance sheet to a personal bank ledger, knowing how to effectively utilize Excel is crucial to success.
If there appears to be an issue with your workbook, retrace your steps to locate the problem. If further assistance is required, visit https://support.office.com/en-us/excel for more help.
Step 9: References
ACC - Beginning Excel. Digital image. Jackson District Library. Joyrce Betz, 30 Oct. 2014. Web. 28 Feb. 2016.
"Excel Help." And Training. Microsoft Excel, n.d. Web. 28 Feb. 2016.
Harvison, Megan. Army Height and Weight Standards. Digital image. EZ Army Points. N.p., 24 Mar. 2014. Web. 28 Feb. 2016.
"Ribbon in Excel." Easy Excel. Excel 2010 Tutorial, n.d. Web. 28 Feb. 2016.