you can use these five little-known tips to make the time you spend in Excel more efficient and productive.
TRACK DOWN CELL DEPENDENCIES
In complex worksheets, where the value in one cell impacts several other cells and formulas, it’s helpful to understand the relationship between those cells—especially if it’s a spreadsheet you didn’t personally create. This tip will help you troubleshoot and understand what’s going on.
Select a cell in your worksheet and then press Ctrl-Shift-] (that’s Control-Shift-Right Bracket) and all of the cells that are dependent on the selected cell will be highlighted.
Bonus tip: With a cell selected, click on Trace Dependencies from the Formula ribbon and you’ll see lines from the cell to the dependent cells. Click Trace Dependencies a second time and you’ll see the dependencies of those cells.
You can even click on the dotted lines and see if the cell refers to a different worksheet or workbook.
USE FORMULATEXT AND THE N FUNCTION
Adding documentation to an Excel spreadsheet probably isn’t very high on your list of things to do at work today. However, spending a few seconds documenting the formulas in your worksheet can save you hours of combing through them a month (or longer) from now to remember how the spreadsheet was set up.
You can quickly document your worksheet using FORMULATEXT and the N function.
In the cell next to your formula (or any cell for that matter), use the function
=FORMULATEXT(cell number goes here)
This will display your formula, making it easy to know how you calculated that value.
Bonus tip: If you don’t want to display the formula on the worksheet but would prefer to describe the formula instead, tack on the N function at the end of your formula.
=(YOUR SUPER LONG FORMULA GOES HERE)+N(your formula description goes here)
The resulting value of the formula won’t change, but you have now described the formula for future reference!
CTRL AND THE SCROLL WHEEL
When you want to quickly zoom in on a section of your worksheet, skip the Zoom buttons on the View Ribbon.
Instead, hold down the Ctrl key on your keyboard and use the Scroll Wheel on your mouse to zoom in and out of your worksheet!
ONLY COPY VISIBLE DATA
When you only want to copy the visible data from a table that has hidden rows and or columns, press Alt-; (alt-semi colon) before you press Ctrl-C to copy.
When you press Alt-; first, it will tell Excel to only copy the visible data and exclude any data or values in hidden rows or columns.
USE DATE & TIME IN YOUR PIVOT TABLES
Using date and time as a metric is one of the most powerful ways to analyze data in a pivot table–except when your data is listed by day instead of Year, Month or Quarter. Here’s how to convert your dates.
In your pivot table, select one of the cells that contains a date in the day format (as seen above) and then Right Click and choose Group.
You can then select how you want to group your dates including by Months, Quarters and Years. Depending how granular your data is, you can choose other options such as Seconds, Minutes, Hours or Days.
Once you click OK, Excel will group your data in a more meaningful way so you can analyze it better and in turn, provide better insights to your business.