Tips from an expert to save time in Excel
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.
How do I navigate in Excel?
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:
- To get to the last row in your data range: Ctrl + down arrow.
- To get back up to the first row: Ctrl + up arrow.
And of course, it also works for columns:
- To get to the last column, hold Ctrl + right arrow.
- To get back to the first column, it’s Ctrl + left arrow.
When you’re navigating in Excel and you want to select several rows or multiple columns, there are techniques to make your life easier:
- To select several rows: Shift + down arrow or Shift + up arrow.
- To select multiple columns: Shift + right arrow or Shift + left arrow.
- To select all the rows in the data range down to the last line, hold Shift: (Ctrl + Shift together) + down arrow. The same thing works to select rows from bottom to top: (Ctrl + Shift together) + up arrow. You can use the same logic for columns: (Ctrl + Shift together) + right arrow (or left arrow).
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.
How can you consolidate data into a single database?
[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:
- Delete duplicates from the Data tab so you have only unique entries. (Data> Remove Duplicates)
- Divide the text into multiple columns, using Text to column in the Data tab. (Data> Text to column).
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:
- What am I looking for? [value to lookup] = the cell in which the information to match is found
- Where? [range in which you want to lookup the value] = range of data in which the answer is found
- If a match exists? = What is the number of the cell in the data range containing the answer to my question? [number of the column in the range containing the returned value]
- If not (if there is no match) then 0 [exact or approximate match, indicated by 0/FALSE or 1/TRUE]
If you want to run a successful vlookup every time, here are a few important tips:
- The column containing the common value always needs to be the first information in the data range.
- Paste the tabs containing the data to be compiled into the same document.
- Use $ to freeze cells in the data range (by hitting F4: once to freeze the column and the row, twice to freeze the row and three times to freeze the column).
- Don’t hesitate to create a mapping table.
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!
How do you use a pivot table?
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!