How to Quickly Crunch Tons of Marketing or other Research Data using Excel

Most of us use Excel to slice and dice our data. Excel is one of those products with some very powerful tools that are easy to use, but you might not know about. It turns out that by learning handful of these tools, you can save yourself hours of time and quickly whip up some revealing analysis. So here are few must know tricks:

1. Pivot Table: Let’s start with A pivot table will allow you to quickly crunch thousands of rows of data into a neat little table of averages. When you are working on complex criteria and large data you might have to manually count and create formula.

•Highlight the data and select the “Insert” tab > “Pivot Table” > “OK” (leave the default options).

•In the “Pivot Table Field List”, drag & drop “Title” into the “Row Labels” box; then drag & drop “Location” into the “Column Labels” box.

•Lastly, drag & drop “Sales Cycle (Days)” into “Values”. The default setting will set this to “Sum of Sales Cycle”. Since we want the average number of days, click on “Sum of Sales Cycle” > select “Value field settings” > select “Average”.

2. VLOOKUP function allows you to match data from two different sources using a unique identifier (e.g. an email address, a customer number, a product code, etc.). For example, say you wanted to incorporate information from a Salesforce.com report into a Company report in order to gain some deeper insight about your customers. Using email address as a unique identifier, you could run a VLOOKUP and Excel would find the information you wanted and add it automatically to a new column in your report.

3. If functions: At its most basic level, Excel's IF function lets you see if a condition you set is true or false for a given value. If the condition is true, you get one result (e.g. "condition is true"). If the condition is false, you get another result (e.g. "condition is false"). The real power of the IF function, however, comes when you string multiple IF statements together, or "nest" them. This allows you set multiple conditions, get more specific results, and -- ultimately -- organize your data into more manageable chunks.

4. Create a Chart: To create a quick Chart from data: Select the data range and press F11 key

5. Other useful tips: 

• a.       Use Name Box: Use the name box on the left top corner to name the selected data
• b.      To add/delete a Row: Select a cell, Press Shift+Spacebar and then Ctrl+ OR Ctrl- to add/delete a row.
• c.       To add/delete a Column: Select a cell, press Ctrl+Spacebar and then Ctrl+ OR Ctrl- to add/delete if column is already selected
• d.      Auto fit column width: If you are going to have lots of text then Auto fit column width: Select the sheet and click format from ribbon and click auto fit column width
• e.      To freeze top row and top column- Select the second cell "B2" and click freeze pane
• f.        To see all the formula in the worksheet: CTRL+
• g.       How to create drop down list:- Use data and data validation and provide data for drop down list
• h.      Remove duplicate- Data menu- Remove duplicate
• i.         Fill the entire column: Select the Cell and double click to fill till the end of the side column
• j.        Quick Analysis of data range - right click and do the quick analysis
• k.       Page break: Page break is used to print different parts of worksheet on separate page. Select row and click on page break to insert horizontal page break and select column and insert page break to insert a vertical page break.
• l.         Macros- To do repetitive task. View Tab- click on record tab and map it to shortcut key to repeat the task easily.
• m.    Gather data from multiple sheets: = Sum(Sheet1:Sheet50!E5) it basically takes data from all sheet from Sheet1 to Sheet10 and sum of E5 cell. You can select multiple sheet by pressing shift
• n.      Trim(H13): use this function to remove extra spaces in the data
• o.      Aging- for example how to find how many days old are you. Put a date =today()-A1
• p.      Concatenate Strings: = concatenate (A1+A2)

(Wait for more update here)

6. Regressions Analysis:
7.  Senstivity Analysis
8. Conditional formating

Ref:
http://www.scu.edu/training/resources/class_resources.cfm?b=404&cat=Excel