Follow these 3 Tricks For a Treat of a Spreadsheet!
/In honor of Halloween, here are 3 tricks you can use to get the most out of your spreadsheets. No dark magic here, just good advice!
1. Use Pivot Tables
We've talked a bit about pivot tables before, but I cannot emphasize enough how powerful they are for data analysis. However, they also have a reputation for being understandable only by very advanced Excel users. This is not true! They are powerful, and it is true that there are many features. Even better, you can get started easily and learn as you go! Check out some of the guides below to get started. Is your data not set up to easily flow into a pivot table? Drop us a line and we can help!
2. Index-Match
Looking up data from one part of your spreadsheet to use somewhere else is common. Many people make use of VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup), which are easy, single-formula options for looking up values. However, there are some limitations. For example, in VLOOKUP, your lookup value must be to the left of the value you want to use. A more powerful and flexible approach combines the INDEX and MATCH functions. To help you choose when to use which function, check out this excellent explanation from Exceljet. Ready to get into even more advanced lookups? Check out all of Excel's lookup functions!
3. Data Tables Instead of Ranges
Excel's data tables are a powerful way to organize large sets of data while ensuring that your formulas remain consistent throughout. I talked a bit about them in a previous post (here), but they are worth taking another look at! One of the biggest advantages of data tables is their built-in use of named ranges and structured references. Referencing columns and tables by name allows you to easily read the formula and better understand what it is doing and what data it is using. Here's an example from an inventory tracking sheet that I've worked on. Without knowing anything else about the spreadsheet you can probably get a reasonable idea of what the formula is calculating just by the names used!
Want to learn more about tables? Check out Microsoft's guide here.
