1) Pivot Tables
Pivot Tables are used to reorganize data in a spreadsheet. They won't change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you'd like them to do.
Let's take a look at an example. Let's say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don't have too much data, but for longer data sets, this will come in handy.
To create the Pivot Table, I go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.
Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
Column Labels: These could be your headers in the dataset.
Row Labels: These could be your rows in the dataset. Both Row and Clumn labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label -- it just depends on how you want to see the data.)
Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.
Since I want to count the number of students in each house, I'll go to the Pivot Table and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.
Excel Tips and Tricks
Mac vs. PC
• If you use a Mac there are a few difference from the PC.
• For instance, Preferences is under the menu Excel.
• Different key strokes may be necessary to do the same trick in a PC.
• The tricks I present here should work on both PC and Macs.
Moving Cells with your Mouse
• Moving cells with your mouse instead of key strokes can be convenient, here’s how.
• Select the range of cells you want to move by highlighting the range with the mouse.
• Position the mouse pointer over the heavy border that surrounds the selected range. The pointer should turn into an arrow(PC) or hand(mac).
• Click and drag the range to a new location. As you move the mouse, the outline of the range moves. • When you are satisfied with the new location, release the mouse pointer. The cells are moved.
Selecting a Range of Cells
• Selecting a long range of cells can be a pain. Here’s one trick that could help.
• Move the cell pointer to the starting cell.
• Press F8 to activate extend.
• Either use your arrow keys or mouse to click on the ending row/column.
• All cells will be highlighted.
• Press Esc to end extend.
Keep Column Names Visible as You Scroll
• Many Worksheets have Headers for each column. As you scroll thru a worksheet it’s helpful to be able to see those names. Here’s how.
• Select the row immediately below the row you want to freeze.
• In the Window menu, click on Freeze Panes. Excel will put a thick line under the row to freeze.
• To unfreeze the row, go to Window menu again and click on Unfreeze Panes.
Creating Multi-lines in a Cell
• You want to type more than one line in a cell and word wrap is not putting the breaks where you need them.
• Click on the cell and type your first line.
• Press simultaneously Control, Option and Return key.
• You should see a new line being created.
When Excel Chooses the Wrong Format for Your Data
• You want 00125678, not 125678
• The format of the cells that you are entering data in needs to be corrected.
• Go to Format and click on Cells. Excel displays the Format Cells dialog box.
• Click on the Number tab.
• In the Category list, choose Text and click okay.
When Excel Chooses the Wrong Format for Your Data
• The gene name is Oct4 not 4-Oct!
• The previous fix for leading zeros will also work here.
• There is one caveat, you must change the format of the cells before entering the data.
• Otherwise 4-Oct becomes 37167
Data Validation
• Data validation guarantees that each data value you enter will be correct and accurate.
• There are many different ways to validate data. Here’ s how to present a list.
• Highlight the cells, column or rows that you want to validate.
• Go to Data and click on Validation.
• A popup menu will appear. Under Allow chose list. Under Source, type your comma delimited list. You can check whether or not you want the drop down menu.
• When you press okay, you should see a drop down menu of your list.
Sorting
• You have 5 columns of data you want to sort and Excel only allows you to sort 3.
• If you want to sort by columns A B C D E, select the whole spreadsheet, than sort by C D E, than A B. This will result in all five columns being sorted.
Sorting
• You have a column of Ids that are F1, F2, ….F150 and would like to sort based on these ids. How?
• The only way to make Excel sort the proper way is to change your ids to F001, F002, etc.
• =LEFT(C1,1) & RIGHT("000" & RIGHT(C1,LEN(C1)-1),3)
Fun with Macros
• Macros can be useful if you do the same process over and over again. Here’s how to create one.
• If you want to ‘record’ steps, Click on Tools, Macro, Record New Macro
• Type in a name for your Macro and perform all the steps you want to record
• Click the stop Recording button when done.
Some Macro Examples
• When you export a tab delimited file from Excel, Excel puts “” around each cell
• So when you export a row of information that has this:
• Wavelength “635”
• When you export it, it becomes this:
• “Wavelength “”635”” “
• Using a macro you can export this file without the extra quotes.
0 comments:
Post a Comment