By Justin Pavoni, from HubSpot Marketing Blog – http://bit.ly/1LJMZT9
Excel is a useful tool for a number of marketing tasks. The two most important uses that I have for Excel on a weekly basis is first, as a research tool while writing (e.g. to get original statistics for a blog), and perhaps more obvious and more common, for data analysis and reporting.
With that in mind, here are ten tricks to help you get more proficient at Excel.
1) How to Summarize Data with a Pivot Table
What is a pivot table? A “Pivot Table” is a spreadsheet functionality that allows you to arrange and categorize tabular data.
Why are pivot tables important? Data is useful only if you can summarize and extrapolate meaningful trends from it. Pivot Tables help you do exactly that.
How do I create a pivot table? Watch the video. Minute 1:05.
2) How to Create a Histogram Using the “=FREQUENCY” Function
What is a histogram? A histogram is a graphical representation (chart) of distribution data. A frequency distribution displays the number of data points that fall within specified ranges in a sample.
Why are histograms important? Histograms are a convenient way to organize data into similar groups (e.g all grades from 90-100 are an “A” while those from 80-89 are a “B”). The range between “90” and “100” is known as a “bin.” Although school grades are less relevant for day-to-day marketing, histograms are commonly used in finance. Since finance affects every single business, understanding how to read, create, and manipulate data in the form of a histogram is critical for business owners and marketers.
How do I create a histogram using the “=FREQUENCY” function? Watch the video. Minute 2:41
3) Understanding Regression Analysis and the Meaning of R2
What is regression analysis? Regression analysis is a statistical process that compares the relationship between different variables in a sample. Excel does all of the hard work for you. The important thing is to understand the meaning behind the numbers that Excel outputs.
Why is regression analysis important? Two words: correlation and causation. It is important to know how much of an impact A is having on B (causation) or if A and B are trending along similar paths without having a direct impact on one another (correlation). R2 is the number you need to understand.
Understanding R2? R2 is known as the coefficient of determination. It describes how well the data fits your chosen model. When you plot your data on a chart, add a trendline (common trendline models are linear and exponential), and then display the R2 value, you will see a number between 0 and 1. Think of this like a percentage. R2 = 0.99 is a 99% fit to the model you picked (pretty darn close). R2 = 0.10 is a 10% fit (not very close).
How do I add R2 to my chart? Watch the video. Minute 6:02.
4) How to Use the VLOOKUP Function
What is VLOOKUP? VLOOKUP is a built-in Excel function that allows you to search your table for a certain value and then outputs its associated value automatically.
Why is VLOOKUP important? Searching a large database manually would take forever, and depending on the size of the table, could be impossible to do without serious errors. If you want to search a database efficiently and accurately then you need to understand VLOOKUP.
Did you know? VLOOKUP stands for vertical look-up – as in looking vertically down a column of data cells. Conversely, HLOOKUP stands for horizontal look-up – as in across a horizontal row.
How do I use VLOOKUP? Watch the video. Minute 8:10.
5) How to Program a Macro
What is a Macro? A macro is a program that records and automatically executes a series of commands (actions) with a single execution instruction (e.g. the click of a button).
Why are Macros important? They make long manual processes simple by automating them.
How do I create a Macro? Watch the video. Minute 10:16.
6) Conditional Formatting
Conditional formatting is useful to identify trends or points of interest in a data set. Watch the video for a few examples of conditional formatting. Minute 11:46.
7-10) Four Keyboard Shortcuts That Are Good to Know
“CMD + 1” : Shortcut into the “Format Cells” Window
“CMD + K” : Insert a hyperlink
“F11” : Creates a chart in a new sheet from the selected data
“CMD + SHIFT + T” : Sums the column of data and puts the total in the next cell (automatically executes the =SUM() function).
Extra Credit: How to Import Data From a Database
You can either download an ODBC driver for the type of database you want to access, or subscribe to a cloud-based service that incorporates the necessary connectivity into their product offering. You will need to determine which database you want to access and download the correct driver for your particular operating system. I will discuss this more in a future blog post.
What is an ODBC Driver? ODBC driver stands for Open Database Connectivity driver. Long answer short: it is required software to access database information. You need different ODBC drivers to access different databases (e.g. Google, Hubspot, Salesforce.com).