7 Excel Tools Beyond Formulas: Sheet Views Privacy, Power Query Folder Tips and Key Limits

The guide covers seven tools: Data Form, Watch Window, Evaluate Formula, Sheet Views, Speak Cells, Goal Seek and Power Query's From Folder connector. We checked each one against Microsoft's own support documentation. The steps mostly match, but several limits matter more than the guide suggests, especially for Sheet Views, which are less private than they sound.

Quick reference: what each tool is for​

1. Data Form: one row at a time, no sideways scrolling​

A table with 30 columns means a lot of horizontal scrolling. A Data Form shows one complete row in a dialog box, with your column headers as the field labels. You can add, find, change and delete rows from there.

Before you start:

• Every column needs a header.
• The range can't contain blank rows.

Setup:

1. Click the arrow next to the Quick Access Toolbar and choose More Commands. (File > Options > Quick Access Toolbar gets you to the same place.)
2. Under Choose commands from, pick All Commands, select Form, click Add, then OK.
3. Click a cell inside your range or table and click the Form button.

Using it:

• Press Tab to move to the next field and Shift+Tab to go back.
• Click Criteria to search. Wildcards work: ? matches one character, * matches any number of characters, and ~ makes Excel treat a literal ?, * or ~ as ordinary text.
• Restore discards changes to the current row, but only before you press Enter.

Common problems, according to Microsoft Support:

• "Too many fields in the data form" means you've gone over the 32-column limit. Microsoft suggests inserting a blank column to split the range in two, then building a separate form for the columns on the right.
• "Cannot extend list or database" means the table can't grow downward without overwriting data below it. Move whatever sits under the table first.
• Deletion is permanent. Once you confirm deleting a row, you can't undo it. Restore won't help here.

Formula cells show their results in the form, but you can't edit the formulas there. You also can't print the form. Think of it as a quick data-entry and lookup tool, not a form designer.

Bottom line: it's ideal for wide, simple lists and nothing more elaborate.

2. Watch Window: keep an eye on cells you can't see​

If you're changing assumptions on one sheet and want to see a total on another sheet move, the Watch Window keeps selected cells in view. It's a floating toolbar that shows each cell's workbook, sheet, name, address, value and formula, and you can dock it at any edge of the window.

Steps:

1. Select the cells you want to watch. Tip: Find & Select > Go To Special > Formulas selects every formula cell on the sheet.
2. Go to Formulas > Formula Auditing > Watch Window.
3. Click Add Watch, then Add.
4. Double-click any entry to jump to that cell.

Two points from Microsoft's documentation that are easy to miss:

• On a Mac, the order is reversed. Open the Watch Window first, then select the cells.
• External links only appear while the other workbook is open. A watched cell that refers to a closed workbook won't show up in the list.

You get one watch per cell. To remove entries, select them and click Delete Watch.

3. Evaluate Formula: step through a formula piece by piece​

Nested IF statements can be hard to read. Evaluate Formula lets you step through the calculation. Select the cell, go to Formulas > Formula Auditing > Evaluate Formula, and click Evaluate repeatedly. Excel underlines the part it's about to calculate and shows each result in italics. If the underlined part points to another formula cell, Step In opens that formula and Step Out takes you back.

Microsoft is clear that the tool won't tell you why a formula is broken, but it can show you where it goes wrong. Keep these quirks in mind:

• Step In isn't always available. It's greyed out the second time the same reference appears in a formula, and for references to other workbooks.
• IF and CHOOSE aren't always fully evaluated. Some branches get skipped, and the evaluation box may show #N/A.
• Blank cells appear as 0.
• Volatile functions can mislead you. RAND, RANDBETWEEN, NOW, TODAY, OFFSET, INDIRECT, INDEX, CELL, AREAS, ROWS and COLUMNS can recalculate, so the dialog may show a different value from the one in the cell.

If a step shows a value that looks impossible, check whether one of those functions is involved before you rewrite anything.

4. Sheet Views: your own filters, but not a private copy​

This is where the MakeUseOf guide oversimplifies. It describes Sheet Views as a "private view" that doesn't change what collaborators see. For sorting and filtering, that's roughly right. In Microsoft's words, you can set up a filter to display only the records that are important to you, without being affected by others sorting and filtering in the document.

Creating one:

1. Select the worksheet where you want the Sheet View, then select View > Sheet View > New.
2. Apply your sort or filter. Excel automatically names your new view Temporary View to indicate the Sheet View isn't saved yet.
3. To keep it, click Temporary View in the Sheet View menu, type a name and press Enter. You can also click Keep.

An eye icon next to the sheet tab shows that a view is active. Hover over it to see the view's name. View > Sheet View > Exit returns you to Default.

What's not private:

• Edits are shared. Microsoft says cell-level edits are saved to the workbook whichever view you're in. Filtering to HR doesn't give you a separate copy of the data.
• Your saved views are visible to others. Microsoft's FAQ answers "Is a Sheet View private, and only for me?" with no: other people who share the workbook can see views you create if they go to the View tab and look at the Sheet View menu in the Sheet View group.
• Showing or hiding rows in Default affects everyone. If you hide or display columns or rows in default view, it persists across all Sheet Views on Excel for Desktop and Mac, and Excel for the web.
• Views don't stay active between sessions. A Microsoft Q&A answer says the view will revert to the Default view after the session ends, so you'll need to pick your saved view again the next time you open the file.

Requirements: You can only use Sheet Views in a document that is stored in a SharePoint or OneDrive location. Sheet Views are supported in Excel for Microsoft 365 and Excel 2021. If you save a local copy of a file that contains Sheet Views, the Sheet Views will be unavailable. If the Sheet View buttons are greyed out, check where the file is saved before you blame your license. The limit is up to 256 views.

Bottom line: Sheet Views are useful for co-authoring, but don't treat them as a way to restrict who sees what. In an HR or payroll workbook, hiding rows in a view doesn't stop anyone from seeing them.

5. Speak Cells: have Excel read your numbers back​

Speak Cells is text-to-speech for your worksheet. It's handy for checking typed figures against a paper invoice without your eyes switching back and forth, and it's also an accessibility feature.

Setup:

1. Customize Quick Access Toolbar > More Commands > All Commands.
2. Add Speak Cells, Stop Speaking and On Enter, then click OK.

Using it:

• Select cells and click Speak Cells. If nothing is selected, Excel expands the selection to neighboring cells that contain values.
• Click Stop Speaking, or select a cell outside the area being read, to stop.
• Turn on On Enter and Excel reads back each entry as you press Enter.

Watch out for: if you hide the Quick Access Toolbar with On Enter still on, Microsoft says Excel keeps reading every entry aloud. Search for "On Enter" to switch it off. Microsoft's documentation lists Excel for Microsoft 365, 2024 and 2021 for this feature, and your PC's audio has to be working.

6. Goal Seek: start from the answer you want​

Goal Seek works backward. You tell it the result you want, and it finds the input that produces it. Go to Data > What-If Analysis > Goal Seek, then fill in:

• Set cell: the cell containing the formula
• To value: the result you want
• By changing cell: the one input Excel is allowed to change

MakeUseOf's example finds the loan principal that gives a $400 monthly payment. Microsoft's example goes the other way: it finds the interest rate you'd need, using a payment formula of =PMT(B3/12,B2,B1). Either works, because the only rule is that the formula has to depend on the cell being changed.

A general Excel point, not from either source: PMT returns a negative number for a positive loan amount, because the payment is money going out. If Goal Seek can't find a solution, check whether your target should be -400 rather than 400.

The main limit is that Goal Seek changes only one input. Microsoft points to the Solver add-in when you need several changing cells or constraints. The MakeUseOf guide says none of its tools need add-ins, which is true, but moving up to Solver does mean enabling one.

7. Power Query From Folder: combine a folder of files​

This is the tool most likely to save hours. Put your monthly CSVs, departmental workbooks or JSON exports in one folder, and Power Query combines them into a single table that you can refresh.

Path: Data > Get Data > From File > From Folder, choose the folder, then pick a Combine option (Combine & Load, or Combine & Transform Data if you want to edit first).

Rules from Microsoft:

• Use a folder for these files only. Every file in the folder is included, and so are files in any subfolders you select. A stray PDF or old backup can break the combined query.
• Keep the schema consistent: same column headers, data types and number of columns. Column order doesn't matter, because columns are matched by name.
• Filter mixed folders first. Choose Transform Data instead of Combine, filter on columns such as Extension or Folder Path, then use Home > Combine Files.
• Skip files with errors is a checkbox that leaves out problem files. It's useful, but check afterward which files were skipped.

Behind the scenes: Power Query uses one example file (the first in the list unless you pick another) to decide how every file should be read. It then creates helper queries: a sample-file query, a Transform File function that runs those steps on each file, and a final query, named after the folder by default, that holds the combined results. Microsoft's documentation calls the sample query "Sample File," while MakeUseOf calls the editable one "Transform Sample File," so the labels in your Queries pane may differ slightly.

Both sources agree on the key point: put cleanup steps, such as promoting headers or removing blank rows, in the sample-file query. Because the sample query and the Transform File function are linked, those steps are applied to every file before the results are combined. The same approach works for files in SharePoint, Azure Blob Storage and Azure Data Lake Storage.

Which versions support what​

Support varies by edition, according to Microsoft's documentation:

• Data Form and Power Query From Folder: Excel for Microsoft 365, 2024, 2021, 2019 and 2016.
• Watch Window: the same Windows versions, plus Mac versions.
• Speak Cells: Excel for Microsoft 365, 2024 and 2021.
• Sheet Views: Microsoft 365 and Excel 2021 (the page also lists 2024), and only for files in OneDrive or SharePoint.

If you share workbooks with people on Excel 2019 or 2016, the Sheet Views part of your workflow won't work for them.

The verdict​

The MakeUseOf guide's main argument holds up: none of these tools needs a new formula, and each tackles a real source of wasted time. Our reservations are about the details. Sheet Views are shared, deleting a row in a Data Form is permanent, Evaluate Formula can mislead you around volatile functions, and Power Query will pull in anything left in the folder.

If you only try one, start with Power Query From Folder if you rebuild the same report every month, or the Watch Window if you maintain big financial models.