Data software often outputs data into a very simple text file that usually isn't compatible with Microsoft Excel's software. In addittion, collected data often is in the incorrect unit or needs to be changed, which is difficult if there is a large amount of information to be transformed. This guide will show the user how to manipulate a large amount of data using Microsoft Office software, showing how to turn a long row of numbers into usable information, and turn that usable information into a graph. This can take just a few minutes for an experienced Excel user, but someone new to the program will need a little longer to figure it out. This tutorial is for Windows, and assumes that the user has Microsoft Word and Excel 2010 or 2013.
Teachers! Did you use this instructable in your classroom?
Add a Teacher Note to share how you incorporated it into your lesson.
Step 1: Learning the Terminology
File Type: Different kinds of files (word document, pictures, PDF’s) require different programs to open them. This is a property of the file called file type. In the past this was denoted by a file extension (.txt, .doc, .mp3) but these are being phased out in favor of icons that indicate the program. Occasionally, files will not have the file type already selected, and the user has to manually select which program can open the file (typically these are text files that can be opened via Notepad).
Text File: A very basic word processing program. Think of it as a simple version of Word, without any of the features besides basic text input. Almost everything that outputs data defaults to producing a text file of that data, because of its simplicity.
Ribbons and Tabs: Two very basic features of Microsoft Office, the ribbon describes everything located at the top of the page when a word document, Excel spreadsheet, or PowerPoint presentation is opened. The ribbon has different tabs, which organize the tools into different groups. A picture of these is shown above.
Step 2: Selecting the Data
The first thing to do is to open the data text file in Notepad. Once this has been completed, select the data that you want to use by either dragging the cursor over the the desired data, or by placing the cursor at the beginning of the selection and then holding "Shift" and clicking the end of the selection.
Step 3: Attempt to Paste Into Excel
Hopefully, the text file was formatted correctly for easy conversion to Excel spreadsheets and you can skip the next few steps. To find out, attempt to past what you've copied into Excel. It should look like a table, with a different number in every cell. However, if something goes wrong, (like in the picture above) then you'll need to do the next few steps.
Step 4: Getting Data Into Word
So now you'll need to use Microsoft Word's 'Convert Text to Table' tool. The first thing to do is to paste the data into an empty word document (it should still be in your clipboard, so no need to re-copy it from the text file!). Side note, your numbers might not look as nicely lined up as mine if you have multiple columns or rows. However, the tool will still work.
Step 5: The Text to Table Tool
To convert text into a table when Excel can't (or won't), the first thing you'll need to do is highlight all the data you've copied into the word document, as shown in the first picture. Then, go ahead and hit the 'Convert Text to Table' button under the Insert tab in the ribbon (shown in the other picture).
Step 6: The Converter Menu
After the Text to Table converter is open, a menu will open up. This menu can be seen in the picture above. The important thing to note is the number of columns that you want, and the bottom section, which identifies where the table will insert lines. The sample data is clearly separated by commas, so Word selected commas as the table separators. However, you can select custom breaks by using the 'other' selection. For example, if your data is separated by six spaces and no comma, typing " " (hitting the space bar six times) into the other box will separate the data every time it sees six spaces. Once the options are correct, hit OK and it will change your data.
Step 7: Getting the Table Into Excel
Now you have a table in your Word Document, but now you need to get it into Excel to turn it into a graph. After you've made the table, you should have it all selected and you can copy and paste it into Excel. If, for some reason, it's not selected, hit the box in the top left corner of the table to select it all.
Step 8: Manipulating Data
Now you should have your table in the Excel spreadsheet. The next step is to manipulate the data as necessary. This step will show the use of very basic operators (+, -, *, /) and how they can be used in Excel.
The most common situation is you’ll want to change a whole column, for example, multiplying column A in the figure above by 10. To begin any operation in Excel, highlight an empty cell that is preferably in the same row as the value you want to use, and type and equal sign (=). This tells the program that you want the number shown here to be equal to the result of this mathematical function. From here, just insert the arithmetic as if you were typing it into your calculator, but instead of typing values from other cells, you can simply use the mouse to select the cell you want to transform. The best part about doing it like this is that after the part of a column has been transformed, copying the formula into the rest of the column will shift the input cell as it copies and pastes, meaning one formula can transform a whole column! Confused by this part? Hopefully the figure above can help. Excel’s formula editor can handle fairly advanced arithmetic, including basic multiplication as well as logarithms and exponents. Numerous guides exist on the Internet, and I won’t go too in depth here.
Step 9: Making a Graph (Part 1)
Now that the table is in Excel, and the numbers are correct, you'll want to put it into a graph. To do that, you'll want to select the data you want in spreadsheet. Select two columns, one for your x-axis and one for the y-axis. Typically, the two are next to each other, and the x axis is on the left, as shown in the picture.
Step 10: Making a Graph (part 2)
After the data is selected, hit the graph button. Typically for scientific fields, the graph you'll want is a simple scatter plot of the data, with no "connect the dots" or any other sort of fluff. That button is found under the Insert tab, in the charts section (as shown in the picture). This looks much different in MS 2013, but the icon should still be the same. The final result will be the graph shown in the other picture.
Step 11: Modifying the Graph
Now you'll want to make the graph look better. Excel gives you plenty of freedom to make the graph look however you'd like it. You can change the colors, add a trendline, and do many other different modifiers. Making very specific types of graphs is covered in many other guides on the internet, and won't be covered in depth here. The most important and basic ones I've pointed out in the picture. Adding axes titles and a graph title are critical to making the simplest of graphs. The final, basic result is shown as well.
Step 12: Conclusion
Now you've successfully transformed a large amount of data into an excellent graph! Along the way, hopefully you learned a thing or two about Microsoft’s Office programs, and how to take advantages of its many features. Although better graphing software exists, Excel is by far the most common, and has many additional everyday uses such as budgeting and anything with a spreadsheet. It has many more powerful tools that were not included in this guide, such as programmable macros and more advanced commands to be inserted into the cells. If any of these pique your interest, there are many many guides just a Google search away! A full video walkthrough of every step we took is shown right above, if anything confused you.