The most useful Microsoft Excel tools and features that are not enabled by default

Excel is packed with productivity features, but some of its most useful tools are hidden or disabled by default. Whether you want faster data entry, better dashboards, or more powerful analysis tools, enabling some overlooked settings can transform the way you work.

Take a dynamic data image

Excel includes a hidden camera tool which can create dynamic snapshots of your data. The tool allows you to display any range as a live image anywhere in the book, making it ideal for build control panels and report pages that update automatically as your data changes.

But before you can take a snapshot, you need to add the command to your interface:

1. Right click anywhere on the Excel ribbon and if you see Show quick access toolbarclick on it. If you don’t, it’s already activated.

2. Right click on Quick Access Toolbar and choose Customize the Quick Access Toolbar.

3. Change the command list to All commands.

4. Select Camerathen click Add to move it to the right menu.

5. Click OK.

Once the icon is visible in your toolbar:

1. Select the range you want to capture

2. Click on the newly added Camera icon at the top of the screen.

3. Click the cell where you want to paste the dynamic image.

You can move and resize the snapshot like any other image, and it automatically updates whenever the source cells change.

Hidden Status Bar Settings

Create a better calculation tracker

The status bar at the bottom of Excel can reveal useful statistics about selected data. By default, highlighting a group of numbers only shows their basic sum, count, and average.

You can dramatically expand this tracker to show deeper metrics, saving you from writing temporary formulas just to check a quick data point. Turning on additional options allows you to see the minimum and maximum values, and the number of numeric entries in the selection.

Doing this will only take you a few seconds:

1. Right click anywhere in the blank space of the status bar at the bottom of the Excel window.

2. In the menu, look for the section that contains the calculation metrics.

3. Click Minimum, Maximumand Numerical Counting to add checkmarks next to them.

Now, whenever you select a range of numbers, Excel will display these additional statistics in the status bar.

SW

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1TB of OneDrive storage, and more.

Automatic decimal point insertion

Speed ​​up numerical data entry

If your daily workflow involves typing hundreds of financial figures or long lists of cents, manually entering decimals can slow you down. Excel includes a built-in automation switch designed specifically to handle fixed decimals for you.

When enabled, you can type continuous streams of numbers on your 10-key keyboard without dots. For example, typing “1550” automatically becomes “15.50” when you press Enter. Unlike Currency or accounting formatwhich only changes the way values ​​are displayed in selected cells, this feature changes the way Excel interprets each number you type, making it useful for high-volume data entry tasks.

Here’s how to activate it:

1. Click Archive and choose Options.

2. Open the Advanced eyelash.

3. Check the box at the top labeled Automatically insert a decimal point.

4. Adjust the Places counter box if you need more than the standard two decimal places.

5. Click OK to activate quick entry mode.

Now, each number you enter will be automatically formatted using the number of decimal places you specified. Just remember to turn off the feature when you’re done; Otherwise, Excel will continue to insert decimals in future entries.

Resolution Plugin

Automate your optimization problems

When you need to find the best outcome for a complex scenario (such as maximizing profits, minimizing costs, or allocating limited resources), doing the calculations manually can be difficult. Excel includes an optimization tool called solver which handles these multivariable problems automatically.

Microsoft leaves Solver disabled by default to maintain the already messy tape clean, so most people don’t even realize it’s there. Once enabled, it adds a dedicated analysis package to your data tools that evaluates different combinations of values ​​to find the best solution based on the rules you provide.

To enable it:

1. Go to Archive tab and select Options.

2. Click Accessories category on the left.

3. Secure the Manage The dropdown menu at the bottom is set to Excel Add-insthen click Go.

4. Check the box right next to Resolution Plugin in the pop-up list.

5. Click OK.

Once enabled, open the Data tab and click solver To define a goal, specify which cells Excel can change and let Solver find the optimal result.

power pivot

Analyze larger data sets with ease

Large data sets can be difficult to analyze efficiently with traditional spreadsheet tools alone. Microsoft includes a powerful data modeling engine called power pivotbut you won’t be able to use it until you enable it as a plugin.

Enabling this feature allows you to import millions of rows of data from multiple sources into a single data model. It allows you to establish relationships between multiple tables without relying on complex lookup formulas, making it easier to analyze large data sets at scale.

For a start:

1. Click Archive tab and open the Options window.

2. Select the Accessories left sidebar category.

3. Expand the Manage drop down menu, select COM Pluginsand click Go.

4. Check the box next to Microsoft Power Pivot for Excel.

5. Click OK.

You can then switch to power pivot tab to add tables to Data modelCreate relationships between data sets and create reports from large collections of data more efficiently.

Optimizing your daily spreadsheet workflow

A few quick menu changes can make Excel much more efficient and unlock tools you didn’t even know were available. Once you’ve enabled these hidden features, spend five minutes make a custom ribbon tab group to further customize Excel and keep your most used commands at your fingertips.