#Excel tip of the day how to#

I prefer creating the table on a new sheet because it is clean and easier to sue. You can either add the Pivot Table on the same spreadsheet or create the table on a new spreadsheet. There are two ways to create a Pivot Table in MS Excel. lead type, date, campaign source, campaign medium. The filter functionality of the Pivot table can be used to filter the data for multiple values, i.e. The excel Pivot Table feature allows you to summarize the data really quickly. Summarizing data from a large Excel spreadsheet is extremely difficult when dealing with multiple variables and filters.

#Excel tip of the day how to#

Here is a short Youtube video that shows how to import value from one spreadsheet to another using Vlookup. For example – data from your CRM solution can be connected to the data from web analytics provided you have one common column (visitor id) between the two spreadsheets. You can pull data from different sources and populate the fields based on a matching column. The possibilities of using Vlookup function are endless. Vlookup can be used to match/compare the data between either the same or across different Excel spread sheets. VLookup is one of the most powerful Excel functions that you can use to match / compare data between two Excel sheets. Lower Control Limit (LCL) = Mean – Standard deviation Upper Control Limit (UCL) = Mean + Standard deviation The control limits can be later applied to the weekly/monthly trend graph. Finally, calculate the upper and the lower control limits. Mean = Average(number1,number2,…,numberN)Ĭ. (hint: For accurate UCL and LCL calculations, it is advisable to have atleast 90 data points).Įxcel Standard Deviation formula = STDEV(number1,number2.,numberN) Enter the ninety day daily visits day on the Excel spreadsheet and calculate the Standard Deviation in an empty cell. Here are the steps to calculate the UCL and LCL for web traffic data.Ī. Using this data, you can focus on when to take action about the data. In any case, your job as a web analyst is to identify a true trend and eliminate noise from data.Ĭalculating the upper and lower control limit (UCL & LCL) using Excel will allow you to establish a trend benchmark for your key metrics. You could see a spike in traffic due to a viral campaign or see a drop in conversion because of shopping cart errors. Your website traffic/conversion/bounce rate can fluctuate due to multiple uncontrollable environmental factors. Establishing control limits for your data set allows you to measure the trends in your web metrics effectively. We have discussed control limits on this blog before, but I could not resist talking about it once again because it is extremely important. In this case, the daily visits show a high positive correlation with conversion (greater than zero is positive and less than zero is negative. The value displayed in the cell (0.963240336) will be the correlation between the avg. Close the bracket and then hit the Enter key.ĭ. daily visits) for the array1 and second data column (avg. Once you hit the Tab key, the cursor will be placed in the bracket, and you will be allowed to select the array data. Press F2 to enter the formula for correlation and type “ =Correl” and hit Tab key.Ĭ. This cell will be used to display the Pearson’s correlation coefficient. Place the cursor on any empty cell in the spreadsheet. Enter the visit and the conversion data on the Excel spreadsheet.ī. Let’s assume that we want to see the relationship between the daily visits and the website conversion.Ī. MS Excel 2007 is equipped with Correlation function and can be used to perform a quick analysis. Pearson’s correlation is the best way to measure the positive and the negative correlation of data to rule out the data inconsistencies and establish a baseline. This happens when one data set (visits) shares positive or negative relationship with another data set (conversion). Sometimes you may notice that your website bounce rate changes with the day of the week, and sometimes the conversion rate will change with traffic. Web data analysis comes with its own set of data inconsistencies and irregularities that cannot be explained by simple math. In this post, we will cover the most important Excel features and tricks to improve the web analytics productivity. Almost all the day to day web analysis and reporting are done in MS Excel and learning Excel techniques to improve productivity will make your life lot easier. Believe it or not MS Excel is the backbone of analytics.