7 Overlooked Excel Features to Save Time: Camera Tool, Analyze Data, Forms and More

Phillips describes his own experience with each feature. Microsoft's support documentation backs up most of the mechanics and adds limits that are easy to miss. This piece covers both.

Custom Lists: stop retyping the same sequences​

Phillips' complaint is that Excel won't autofill the alphabet, and that he kept retyping project-specific lists such as team names or budget categories. His fix is Custom Lists, which you reach through File > Options > Advanced > Edit Custom Lists. You save a sequence once, then use it for fills and sorting.

Microsoft's documentation describes custom lists as a way to sort or fill in a user-defined order. Its own examples are things with no natural order, such as High/Medium/Low or North/South/East/West. Excel ships with built-in weekday and month lists. You can't edit or delete those, but you can add your own.

Some practical points from Microsoft's documentation, which describes the Windows desktop path as File > Options > Advanced > General > Edit Custom Lists:

• Short lists: type the entries directly into the dialog box, one per line.
• Long lists: type the values into a worksheet range, select it, and import it from the dialog.
• Numbers-only lists: a list of plain numbers, such as 0 to 100, has to be created as text first.
• Values only: you can't build a custom list from formatting such as cell color, font color, or icons.

The sharing caveat. Lists you create are stored in your computer's registry, so they're available across your workbooks. If you sort with a custom list, that list also travels inside the workbook. Someone opening the file on another machine will see it in the Sort dialog's Order column. They won't see it in their Options dialog, and it isn't immediately available for Fill. They can add it to their own machine from the Sort dialog if they want it for filling. If you build templates for a team, this explains why a fill works for you and not for a colleague.

One note on the alphabet example: it's a sensible use, but Microsoft's pages illustrate custom ordering rather than alphabet generation. The reusable-sequence idea is the documented part.

The Camera tool: a live picture of a range​

Phillips used to rebuild dashboard cells with formulas like =A1, or recreate charts to fit a layout. The Camera tool instead makes a linked picture of a cell range. It can be moved and resized like any image, and it keeps updating as the source data changes.

Microsoft confirms the core behavior: because the cell range is linked to the graphic object, changes to the data appear automatically in the object. The documented setup, which matches what Phillips describes in outline, is:

1. Click the arrow next to the Quick Access Toolbar and choose More Commands.
2. Under Choose commands from, select All Commands.
3. Pick Camera, click Add, then click OK.
4. Select a cell range, click Camera on the toolbar, then click where you want the picture to land.

You can right-click the picture and use Format Picture to change the border or make the background transparent. Microsoft's page lists Excel for Microsoft 365, 2024, and 2021 as applicable. The research for this piece didn't turn up Microsoft instructions for the cropping and chart-over-range trick Phillips describes, so treat that as his workflow.

The picture is not a static screenshot. If you paste a Camera image into a deck or email and expect it to freeze, you may be surprised.

The Navigation pane: one place to find things​

Phillips had been juggling the Name Box and Ctrl+F. He now uses the Navigation pane, which gathers workbook elements in one spot. Microsoft's documentation applies it to Excel for Microsoft 365 on Windows and Mac. Older perpetual versions aren't listed.

To open it, go to View > Navigation. The pane appears on the right. Each sheet gets its own expandable section listing tables, charts, PivotTables, and images. Selecting an item jumps to it, switching sheets if needed.

What you can do from the pane depends on the object:

• Charts and images can be renamed, deleted, or hidden.
• PivotTables can only be renamed.
• Tables have no right-click options.

Microsoft also points out that the pane helps people who use screen readers. A workbook full of default names like "Chart 7" is awkward for everyone, so renaming objects is worth the effort.

Analyze Data: suggestions without a full Copilot session​

Analyze Data, formerly called Ideas, is for exploring a dataset without a full AI conversation. Phillips opens the pane and lets Excel suggest charts and summaries. You can type a natural-language question if you want to steer it. On his tblSales table, he says it suggested a PivotTable of sales by department and country plus a chart of sales by product. That is his reported result, not something we reproduced.

Microsoft's guidance is more specific than "use clean data":

• Start: select a cell in your data and click Analyze Data on the Home tab. Microsoft's Windows tip is to format data as a table with Ctrl+T.
• Headers: use a single row of unique, non-blank column headings, with no double header rows or merged cells.
• Focus it: you can pick the fields you care about and how to summarize them. Microsoft's example is sum of sales by year.

If it returns nothing, Microsoft lists likely causes:

• Size: it doesn't currently support datasets over 1.5 million cells. Microsoft's suggested workaround is to filter and copy a subset elsewhere.
• Text dates: strings like 2017-01-01 are treated as text. Create a new column with DATE or DATEVALUE and format it as a date.
• Old file formats: it won't work in compatibility mode, such as .xls files. Save as .xlsx, .xlsm, or .xlsb.
• Merged cells: remove them and use Center Across Selection for centered headings (Ctrl+1, then Alignment).

Even with perfect data, it may find nothing. Microsoft says the service looks for a limited set of insight types: rank, trend, outlier, and majority. It is also a cloud AI service. Microsoft points users to its privacy statement for data-handling questions, which matters if your workbook holds sensitive figures.

Go To Special: select by what a cell is​

Phillips uses Go To Special to select cells by property instead of clicking them. In Excel for Windows, press Ctrl+G and choose Special. Alternatively, go to Home > Find & Select > Go To Special.

Microsoft's documented options include:

• Blanks, constants, formulas, and notes
• Current region and current array
• Objects such as charts and buttons
• Row and column differences
• Precedents and dependents
• Last cell
• Visible cells only
• Conditional formats and data validation

Select a range first to limit the search. Select a single cell to search the whole worksheet. Phillips selected blanks in a dataset and filled them yellow so he could review the gaps. Go To Special finds the blanks, but it can't tell you which ones are errors or what belongs in them.

Visible cells only. This option matters when you copy from a range that includes hidden rows or columns. Microsoft notes that this option isn't available in Excel for the web.

Grouping: collapsible detail instead of hidden rows​

Phillips stopped hiding rows and columns because hidden data is easy to forget about. He now groups them under Data > Outline > Group. Related rows or columns collapse behind outline controls and can be expanded when needed.

Microsoft's documentation adds detail:

• Depth: outlines can have up to eight levels.
• Data layout: the data needs a label in each column's first row, with no blank rows or columns in the range.
• Automatic outline: Data > Outline > Group > Auto Outline can build one for you.
• Manual grouping: display all data first, so you don't group the wrong rows or columns.
• Summary rows: by default Excel expects them below the detail. You can change that in the Outline settings dialog.
• Ungrouping: if detail is hidden when you ungroup or remove an outline, it can stay hidden. Drag across the adjacent row numbers or column letters, then use Home > Cells > Format > Hide & Unhide.
• Missing symbols: if the outline symbols don't appear, check that "Show outline symbols if an outline is applied" is ticked under File > Options > Advanced.
• Excel for the web: you can group rows and columns there, but you can't set styles or positions for summary rows.

Grouped data is still hidden when collapsed. The difference is that the +/- controls and level numbers show that something is collapsed.

The Form: one record at a time​

Phillips had been scrolling sideways to enter data in wide tables. He also worried about overwriting a formula by clicking the wrong cell. The built-in data form shows one record as a vertical list of labeled fields.

Microsoft says a data form is useful when a row of data is very wide and requires repeated horizontal scrolling. It lists Excel for Microsoft 365, 2024, 2021, 2019, and 2016. The Form button isn't on the ribbon by default. Microsoft's documented route is to add it to the Quick Access Toolbar through More Commands, with Choose commands from set to All Commands.

Before you use it:

• Give every column a header, because Excel uses the headers as field labels.
• Make sure the range has no blank lines.
• Click a cell in the range or table before opening the form.

Things to know:

• 32-column limit: the form holds up to 32 columns. If you see "Too many fields in the data form," Microsoft suggests inserting a blank column to split the range in two. You can then create a separate form for the columns on the right.
• Formulas: the form shows a formula's result, but you can't change the formula from it. That does reduce the risk of overwriting one by accident.
• Undoing edits: before you press Enter, Restore discards what you've typed. Pressing Enter commits the row, and Excel expands the table or range downward.
• Searching: the Criteria button filters on text that begins with your criteria. Typing "Dav" finds both Davidovski and Davis.
• Deleting: one independent tutorial site, Excelguru, says Delete removes the record immediately with no undo. Treat that as a reason to be careful, since Microsoft's page doesn't state it in the excerpts we reviewed.
• Limits of the tool: it's plain text boxes. If you need list boxes or spin buttons, Microsoft says to look at custom form controls.

Quick reference: versions and setup​

Our take​

Phillips' list is a personal account, so how much time you'd save will vary. Microsoft's documentation doesn't establish how many users overlook these features. It does confirm that all seven exist and work roughly as he says.

The fine print is where the practical value sits. The Navigation pane and Analyze Data are tied to Microsoft 365. The Camera tool and Form need a Quick Access Toolbar change. Analyze Data fails on text dates, merged cells, and .xls files. The Form stops at 32 columns.

If you only try three, start with Go To Special's blanks and visible-cells-only options for cleanup. Add Grouping for tidier reports. Then try the Camera tool the next time you build a dashboard.