If you like this presentation – show it...
5 OFFICE 2016. EXPLAINED. Quick Tips! Excel Tips You’ll Actually Use These 5 little-known tips for Excel will help you troubleshoot complex worksheets, document complex formulas, zoom in on your spreadsheets, copy visible data only and learn a new way to organize your pivot tables. GET STARTED
TIP 1 Tracking down cell dependencies When you have a complex worksheet, where the value in one cell impacts several other cells and formulas, press Ctrl-Shift-] (right bracket) to see the dependencies. NEXT SLIDE
Quick tip! You can even click on the dotted lines and see if the cell refers to a different worksheet or workbook. 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.
TIP 2 Document formulas Use FORMULATEXT and the N function to quickly document the formulas in your worksheet. 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. NEXT SLIDE
Quick tip! TIP 2 Document formulas using +N If you don’t want to display the formula on the worksheet, at the end of your formula add +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! NEXT SLIDE
TIP 3 Quick zoom Ctrl + 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! NEXT SLIDE
TIP 4 alt + ; Copying visible data from a table with hidden rows/columns When you only want to copy the visible data from a table that has hidden rows and or columns, use this little known keyboard shortcut before you press Ctrl-C to copy: Alt-; (alt-semi colon) When you press Alt-; ﬁrst, it will tell Excel to only copy the visible data and exclude any data or values in hidden rows or columns. NEXT SLIDE
TIP 5 Date & time as a pivot table metric 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. NEXT SLIDE
Before In your pivot table, select one of the cells that contains a date and then Right Click and choose Group.
After This will group your data so you can analyze it better.
FOR MORE TIPS ON GETTING THE MOST OUT OF EXCEL, VISIT LYNDA.COM/EXCELTIPS