3 little-known Excel tools worth exploring this weekend (September 4-6)

Excel has plenty of features that can make everyday spreadsheet work easier, but some of the most useful ones are surprisingly easy to overlook. If you have a little time to experiment this weekend, try three of my favorite hidden gems.

Custom Lists can teach Excel your own order

Make Excel fill and sort the way you actually work

Excel already knows that Monday comes after Sunday and February comes after January. But you can also teach it sequences that are specific to how you work. Custom Lists let you create your own order and then use it with Excel's fill handle or sorting tools. For example, a project tracker might use three priorities: High, Medium, and Low.

To create this sequence:

1. Go to File > Options, then open the Advancedmenu.

2. Scroll to the Generalsection and click Edit Custom Lists.

3. Select NEW LIST.

4. Enter the items in the order you want them.

5. Click Add, then OK.

Now type the first wordin your list into a cell and drag the fill handle down. Instead of repeating that word, Excel continues with the next items in the sequence you created. You can also drag the sequence across a row if you want to create a set of column headings or another repeating structure.

You might be thinking this is only useful for niche scenarios, but Custom Listsreally earn their keep when you need to sort data according to your own logic. Excel's standard alphabetical sort would put these priorities in the order High → Low → Medium, but that's rarely useful. With a custom list, you can have Excel sort the data according to the order you've defined. Here's how:

1. Select your table.

2. Go to Data > Sort.

3. Select the columnyou want to sort.

4. Choose Customized Listfrom the Ordermenu.

5. Select the custom listyou created, then click OK.

Excel sorts the entire table according to that hierarchy.

You can also import a longer sequence from an existing worksheet rather than entering each item manually. Once created, the list is saved in Excel's settings, so you can use it again in other workbooks on the same computer.

Excel's Camera tool can put live data anywhere

Turn a range into a picture that stays up to date

Copying cells from one part of a workbook to another is easy enough, but a normal copy can quickly become outdated when the original data changes. Excel's Camera tool creates an image of a cell range that stays linked to the original cells. Change the source data, and the picture updates too.

This can be particularly useful when creating a dashboard . Suppose a detailed monthly budget sits on one worksheet, while a separate dashboard needs to display those figures. Instead of copying the cells and maintaining a second version, use Camerato display the original range on the dashboard.

First, you'll need to add Camerato your Quick Access Toolbar(QAT):

1. Click the down arrowon the right-hand side of any tab on the ribbon. If you see Show Quick Access Toolbar, click it to activate your QAT.

2. Click the QAT down arrowand select More Commands.

3. Select All Commandsin the Choose commands frommenu.

4. Scroll to and select Camera, click Addto add it to your QAT, then click OK.

You'll now see Camera in your QAT.

Now, select the cellsyou want to display, click Camera, move to the destination worksheet, and click and dragto place the picture. The result looks like an ordinary image while remaining connected to the source range. If a budget figure or some formatting changes in the original cells, the corresponding figure in the image changes as well.

The captured range can also be moved and resized, making it easy to position alongside charts, headings, or other dashboard elements.

Camera works best with fixed ranges. If the source is an Excel table that regularly expands, newly added rows may not appear in the captured image. And if you want to capture a chart or another object, select the cells behind it rather than the object itself.

Analyze Data can find insights without formulas

Let Excel do some of the investigative work

When you're working with a large table of records, finding useful patterns can mean building formulas, creating PivotTables , or manually sorting and filtering the data. Analyze Data (previously called Ideas) can give you a useful starting point much faster. If you're using Excel for Microsoft 365 and have an internet connection, you can use this tool to automatically examine a structured dataset for patterns and summaries.

To use it, select a single cellinside your data, then click Analyze Datain the Datatab. In some versions of Excel, this button is found in the Hometab. Excel opens a pane containing automatically generated insight cards based on the information it finds.

You can also type a question into the box at the top of the pane. For example, because this dataset contains "Genre" and "Rating" columns, you could ask, "What average rating does each genre have?" Excel analyzes the data and produces a result that may include a summary table or visualization.

If the result is useful, click the contextual Insertbutton to add the generated chart, PivotTable, or PivotChart to your worksheet. You can also try one of the suggested questions Excel generates based on your data.

Analyze Data works best when the source is properly structured . Make sure your data has a single header row and avoid unnecessary blank rows or columns. Formatting the range as an Excel table with Ctrl+Tis also a good idea.

---

There's always more to discover in Excel

This weekend is as good as any to explore some of Excel's lesser-known features. Custom Listscan make Excel follow your preferred order, Cameracan keep a live view of important cells elsewhere in a workbook, and Analyze Datacan quickly uncover patterns in a table. There's so much hidden away in Excel that the discovery never really ends. So, if you still have some time to explore, take a look at some little-known Excel clicks that can further power up your spreadsheet skills.