![]() 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.ĭiscover more ways to get the most of Excel from Dennis Taylor’s course Excel Tips.Whether you are a Microsoft Excel beginner or an advanced user, you'll benefit from these step-by-step tutorials. Depending how granular your data is, you can choose other options such as Seconds, Minutes, Hours or Days. You can then select how you want to group your dates including by Months, Quarters and Years. 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. 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. ![]() ![]() 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. 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. 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 want to quickly zoom in on a section of your worksheet, skip the Zoom buttons on the View Ribbon. ![]() The resulting value of the formula won’t change, but you have now described the formula for future reference! Ctrl+Scroll Wheel =(YOUR SUPER LONG FORMULA GOES HERE)+N(your formula description goes here) This will display your formula, making it easy to know how you calculated that value.īonus 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. In the cell next to your formula (or any cell for that matter), use the function =FORMULATEXT(cell number goes here) You can quickly document your worksheet using FORMULATEXTand the N function. 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 even click on the dotted lines and see if the cell refers to a different worksheet or workbook.Īdding documentation to an Excel spreadsheet probably isn’t very high on your list of things to do at work today. Click Trace Dependencies a second time and you’ll see the dependencies of those cells. 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.īonus 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. This tip will help you troubleshoot and understand what’s going on. 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. There are thousands of Excel tips out there-many of which you’ll rarely need to use.īut you can use these five little-known tips every day to make the time you spend in Excel more efficient and productive.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |