Microsoft Excel for Data Analysis
For the purpose of this tutorial, we shall use Excel 2013 and 2010. However, most formulas and methods discussed can be applicable to Excel 2007.
Many people outside the field of data analysis understand the power of MS Excel in data analysis. Perhaps this is because they are little aware of the many statistical programs we have for the purpose of data analysis. However, those in the field of statistics tend to forget or ignore the power of excel in data cleaning, manipulation and even analysis and visualization. One of the advantages of using Excel is that it is easy to use. Its functions are easy to apply and you don’t need to memorize all of them.
Now, let’s walk together step by step and I will show you however we can enter raw data into excel, clean it, perform some basic statistical procedures and visualize our data using charts and graphs.
NOTE: For some later part of this series of tutorials, you must have Analysis ToolPak. The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.
If you do not have the Analysis ToolPak here are the steps to follow in order to load and activate it.
i. Click the File---Options----Add-ins
ii. In the Manage box, select Excel Add-ins and then click Go
iii. Check the Analysis ToolPak then click OK
• If Analysis ToolPak is not listed in the Add-ins available box, click Browse to locate it.
• If you are prompted that the Analysis ToolPak is not currently installed in your computer, click Yes to install it. https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4?ui=en-US&rs=en-US&ad=US&fromAR=1
Excel Tutorial 1
In this tutorial, we are going to learn the basics of data entry.
Part 1. Simple Data Entry
1. Control cursor movement with TAB and ENTER
The simple combination of using the TAB and ENTER keys will help you enter data in rows.
Use TAB and ENTER to enter your data in rows
The standard setup in Excel is to press TAB to move your active cell to the right by one cell, and press ENTER to move your active cell down by one cell.
So when you want to enter data in rows just follow these steps:
• Move to the first cell in your row
• Type in the first value
• Press TAB to move your active cell to the right
• Type in the second value
• Press TAB to move your active cell to the right
• … repeat until you get to the last column
Press ENTER to move your active cell to the next row (Excel remembers which column you started from and automatically jumps one row down and all the way back to that first column)
If you need to go back don’t use the arrow keys. Simply use SHIFT + TAB to the previous cell on the same line. And you can use SHIFT + ENTER to go one cell up.
2. Fill down from above using CTRL + D
You can fill in content from above using the keyboard shortcut CTRL + D. This means that formulas and values are copied down.
Fill in one cell or one row of cells from above
Before filling down one row using CTRL + D
After filling down one row using CTRL + D
Move your cursor to the cell below the one you want to copy.
Press CTRL + D and the contents of the cell above are copied into your active cell. (Note: whatever was in the cell will be overwritten)
You can also select several cells before pressing CTRL + D. The contents of the cells above your selection are copied into your selected range of cells. (Note: whatever was in the cells will be overwritten)
Fill in many rows of cells from above
Before filling down many rows using CTRL + D
After filling down many rows using CTRL + D
Select the cells that you want to copy downwards.
Hold SHIFT and press the down arrow to select the cells you want to copy into. Then press CTRL + D to copy the contents of the first row of cells downwards. (Note: whatever was in the cells is overwritten.)
You can also fill to the right using the same technique but with CTRL + R to fill right.
3. Autocomplete using Tab key
Here’s a tip that saves you time if you need to enter data that’s already been entered somewhere in the column above.
Let’s say you have entered “John” on row 1.
When you move down one cell to row 2, start typing “J”. Excel automatically enters the full name “John” and highlights the “ohn” black so you can accept or reject Excel’s suggestion.
Excel will complete only those entries that have text or text with numbers.
It doesn’t look for numbers, dates or times. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entry.
Excel autocompletes data based on existing entries in that column.
After Excel completes what you started typing, you have different options:
Press TAB to accept the entry and move one cell right.
Press ENTER to accept the entry and move one cell down.
Continue typing to enter a different value.
Press BACKSPACE to delete the automatically entered characters.
If you have several names beginning with “J” then you need to type a few more characters until Excel can suggest the correct value.
For instance if you have already entered “John”, “Juliet” and “Jomo” in one column and want to enter “John” then you need to type “Joh” and Excel will then fill in the rest of the name.
4. Show drop down list using ALT + Down arrow
You can display the AutoComplete list by pressing ALT + Down arrow.
Once the dropdown list is visible you can either use the arrow keys and ENTER to select a value, or click on the value to select it.
This works either in a cell with dropdowns or when you have selected an autofilter as you can see below.
Another way to show a drop down list is with Excel’s Data Validation feature, which we’ll cover in another tutorial.
5. Enter values into multiple cells using CTRL + ENTER
Scenario 1 – quickly enter data in multiple cells
To quickly enter data in multiple cells, first select all of the cells.
Type in the data and press CTRL + ENTER, which enters the same value in all of the selected cells.
First select all the cells you want to fill
Type 'John' then enter the same value in all selected cells using CTRL + ENTER
For non-contiguous cells, hold down the CTRL key and click to select each cell.
To enter values in non-contiguous cells first hold down the CTRL key and click to select each cell then type in 'John' and use CTRL + ENTER to enter the same value in all selected cells
Scenario 2 – quickly correct data in multiple cells
To quickly correct the data in multiple cells, select all of the cells containing the names you want to fix.
Let’s say you need to replace Betty with Britney in five cells.
How do you quickly replace Betty with Britney in these five cells?
First select all give cells containing Betty. For non-contiguous cells, hold down the CTRL key and click to select each cell. You can then type Britney and press CTRL + ENTER, which will enter “Britney” in all of the selected cells.
Type "Britney" and press CTRL + ENTER to enter "Britney" in all selected cells
Scenario 3 – quickly fill upwards
If you would like to fill up instead of fill down, you can do this by a clever modification of the CTRL + ENTER trick.
Let’s say you want to replace Britney with Charlie. There’s already a “Charlie” entered in the cell below. Select the cells starting with “Charlie” and ending with the topmost “Britney”
How do you fill 'Charlie' upwards to overwrite the 5 cells showing 'Britney'?
With the cells selected, press F2 to go into Edit mode, then press CTRL + ENTER to copy the correct name into all of the selected cells.
With the cells selected as shown, press F2 then press CTRL + Enter and 'Charlie' has been filled upwards to replace 'Britney'.
Comments
Post a Comment