Summary
Anyone who uses Excel is probably looking for something that makes their sheets simpler and easier to manage. Often, that means learning more efficient Excel formulas or catching up on newly released ones. However, I’ve learned you can save even more time by mastering features already built into Excel. Instead of constantly learning new formulas, you can take note of these tools and use them to save hours of scrolling, guessing, and double-checking. Data Form A simpler way to work with wide tables Whenever I’m working with very wide spreadsheets that require me to scroll from far left to far right quite frequently, I’m always thankful that I can use a Data Form. It turns your unwieldy table into a simple pop-up box that allows you to view, edit, enter, search, and delete complete rows of data without scrolling back and forth. Excel automatically builds the Data Form using your existing column headers as field labels, which is why you’ll find all your column headers listed beside the text boxes. Every column in your dataset must have a header, and the range cannot contain blank rows. This tool won’t be on your ribbon by default; you’d have to enable it. To do that, go to File > Options > Quick Access Toolbar > Choose commands from: All Commands, then select Form, click Add > OK. Once it’s in your QAT, you can select the relevant range and click the icon. After the form is generated, you can search with wildcards using the Criteria button and even click Restore before hitting Enter to undo any changes you’ve made to a row. Data Forms are limited to 32 columns, can’t be printed, and don’t allow you to edit formulas through the form. Watch Window See what’s changing without jumping around If you have many cells that depend on others spread across a large workbook, you might want to see how things change without constantly jumping between tabs. Excel’s Watch Window helps out by pinning key cells, their values, formulas, and locations into a floating toolbar that you can dock anywhere on your screen. To access the Watch Window, go to the Formulas tab > Formula Auditing group > click Watch Window > click Add Watch. Once it’s enabled, you can double-click any entry to jump straight to that cell, even if it’s on another worksheet or workbook. Remember, you can add only one watch per cell, and external workbook links refresh only while the file is open. Evaluate Formula Take the guesswork out of complex formulas Nested formulas, especially those with lots of IF statements and logical tests, are one of the toughest parts of Excel, but the Evaluate Formula tool makes them much easier to understand. Evaluate Formula walks you through each calculation step by step, underlining the part being solved and showing the results in italics. You can even “step into” nested formulas for a closer look at how each part is being calculated. It only works on one cell at a time, but that’s usually enough to help you untangle a complicated formula. You’ll find this feature by selecting the relevant cell and going to the Formulas tab > Formula Auditing group > Evaluate Formula. Sheet Views Because not everyone needs the same view When you’re working on an Excel spreadsheet with others, you can use Sheet Views to filter and sort your own private view without changing what your collaborators see. For instance, you can filter the whole dataset to display only rows that belong to the HR department, if that’s the only department you’re concerned with. This filter won’t affect your colleagues, who can still see all the other departments and any data edits you make, unless they apply a Sheet View of their own. Go to the View tab > Sheet View > New to create a custom view. Once you do, you should see a small eye icon next to the worksheet tab, which shows that a custom view is active. You can Keep the view if you or anyone else would like to use it some other time. Each worksheet can have up to 256 Sheet Views, so you and your colleagues have plenty of room to create customized views and work efficiently. Just note that this feature requires your file to be stored in OneDrive or SharePoint, so locally saved workbooks are out of luck. Speak Cells Sometimes it’s easier to hear the numbers Speak Cells reads your spreadsheet aloud, including cell contents, ranges, or entire worksheets, so you can verify numbers against a printed invoice or report without making your eyes bounce back and forth. To enable this tool, go to the File tab > Options > Quick Access Toolbar > Choose commands from: All Commands. Select Speak Cells, Stop Speaking, and On Enter, then click Add > OK. Once enabled, click Speak Cells on Enter in your QAT, and Excel will read each cell aloud as soon as you press Enter. Alternatively, you can select a range and click Speak Cells, and Excel will read the contents aloud. If you don’t highlight a range, Excel will expand the selection to include neighboring cells containing values. Goal Seek Let Excel figure out what needs to change Sometimes, instead of applying a formula to get a value, you already know the value you want but aren’t sure which components you need to get there. For instance, you might know that you want a loan payment of 400 a month. You’d go to the Data tab > What-If Analysis > Goal Seek. From there, enter the cell containing your formula in Set cell, the exact value you want to achieve in To value, and the cell Excel should adjust in By changing cell. Bear in mind that Goal Seek can only adjust one input cell to reach your target output. If you want to juggle multiple variables, you’ll need the Microsoft Excel Solver add-in instead. Power Query (Import Data from a Folder) Turn a folder full of files into one table With Power Query’s From Folder connector, you can combine multiple files with a compatible structure (CSVs, Excel workbooks, or JSONs, etc.) from one folder into a single output table. Your target files need a consistent schema, with matching column names and data types, but the column order doesn’t matter. When you use From Folder > Combine, which you’ll find under the Data tab > Get Data, Power Query creates these supporting queries: | Example/Sample File | This is the file Power Query uses as the model for determining how each source file should be interpreted. By default, it’s the first file, but you can choose another. | |---|---| | Transform Sample File | This contains the transformation steps used to extract and clean data from one file. | | Transform File (Function) | This turns those transformation steps into a function Excel can apply to each file in the folder. | | Final/Output Query | This applies the function to the files and combines the resulting data into the final table. | The key is knowing where to make your edits. If you need to clean up headers, blank rows, or other recurring formatting issues, make those changes inside the Transform Sample File. Power Query will then apply those same steps to each file before combining the results. Before you learn another formula, try these None of these tools require you to learn a new formula, and each one can shave real time off your week. You also don’t have to download any add-ins. Most are already sitting on your ribbon, while a few simply require you to enable them. Either way, you only need a few minutes to save yourself valuable time.