Whether Excel makes up 70% of your job and you’re convinced there’s a faster way to do things, or you have to run a monthly consolidated data report that makes you tremble with anxiety every time, don’t worry!
We have solutions to save time and make your life easier—and there are no lengthy tutorials involved!
To get there, you’ll need to know how to: navigate, compile data and create the perfect table that updates automatically. This will considerably reduce the time and effort you need to put in as well as your margin of error.
To save time navigating through Excel, there are keyboard shortcuts and filters that will allow you to be more efficient in your searches. In fact, to add a filter to your Excel sheet, you just need to hold (Ctrl + shift +L). This functionality will make it easier to quickly sort and select the information you need. You can reverse the filter by using this same formula.
Along the same lines, when your document has thousands of rows and hundreds of columns, you absolutely need to know keyboard shortcuts to avoid scrolling for hours.
For a row, a column or from a cell, you will save precious time in your search for information:
And of course, it also works for columns:
When you’re navigating in Excel and you want to select several rows or multiple columns, there are techniques to make your life easier:
Now that you can navigate like a fish in water between rows and columns, it’s time to master data processing, an essential aspect of analyzing information.
[video width="982" height="778" webm="https://www.adviso.ca/app/uploads/2017/10/Video-2-Vlookup-1.webm"][/video]
Because the data sources you want to analyze can vary, you need to process the data. This gives you the opportunity to compile your data, and eventually to add complementary information to facilitate your subsequent analysis.
Here are some tips if you want to quickly create a mapping table:
In media, for example, if you have a list of campaigns with a uniform nomenclature like (Product_market_language): (Dress_Canada_EN), (Dress_Canada_FR), (Dress_USA_EN), this function would allow you to create three columns out of the information (product | market | language), and your data set would be ready to go.
Always with the objective of simplifying things for your data analysis, here are a few formulas and functions that will feel like magic.
The number one function to master to save time is vlookup. Office support describes this function as: value you want to look up, range where you want to look up the value, the column number in the range containing the return value, Exact Match or Approximate Match—indicated as 0/FALSE or 1/TRUE.
Which amounts to saying:
If you want to run a successful vlookup every time, here are a few important tips:
Now you can add information to the data range that didn’t appear in your data export, but that you have in another file or mapping table.
When all your data is in a single data range, you’ll be able to create a pivot table, which will allow you to create your synthesis in a dynamic table and make it easier to analyze and read your data.
However, a pivot table can become complex when you don’t know everything it can do. But of course, we have the solution!
First off, to create a pivot table, select your data range then click PivotTable from the Insert tab. Ideally you should select the columns in your data range, that way, if rows of data are added afterwards, the pivot table can be easily updated using Analyze> Refresh. That will save you from wasting time starting your pivot table over from scratch!
From there, you’ll need to recalculate all the ratios from your list of formulas using Analyze> Fields, Items & Sets> Calculated field), like, for example: click rate, conversion rate, CPC, CPA, CTR, bounce rate, etc.
To avoid errors, here’s one critical piece of advice: think about deleting the ratios in your data range before making the pivot table, that way you’ll be forced to make correct calculations.
Once your data is collected in the table, you need to organize it. You can play with the levels (market/language/format or format/language/market) to figure out which view meets your needs best. At the same time, you can use the drag and drop function from the Row labels column and create a filter by directly selecting data in the table (right click> filter> Keep Only Selected Items).
[video width="1344" height="778" webm="https://www.adviso.ca/app/uploads/2017/10/Video-3-Organisation-pivot-table-1.webm"][/video]
One last tip: to format the numbers in your table, don’t select the cells in your table, but rather the columns of the table itself. If you modify the order or add sub-totals, the formatting will be preserved. All that’s left is for you to play with the visualization options in the Design tab.
Now that you have the table you need, you might ask yourself if the process is maybe a bit too long. Wait! There’s one more detail to add: You can reuse the table forever! As long as you’re using the same columns in the same order, all you need to do is update the data and refresh the table from the Analyze tab (Analyze> Refresh).
Using Excel may make you break out in a cold sweat, but don’t forget, the more you practice, the easier it gets!