Technology

7 Handy Microsoft Excel Features You Might Have Missed

Written by admin

Sometimes when you use apps every day, new features can go unnoticed. For useful spreadsheet tools, take a look at these Microsoft Excel features you may have missed.

1. Sparklines for fast little charts

While you can create many different types of charts and graphs in Excel, the sparkline option is something you might not be looking at. With it, you can insert a useful graph into one cell without being distracted from the data.

Select the cell where you want to add the sparkline. Click the Insert tab and select a Line, Column, or Win/Loss Plot, depending on the style you want, from the Sparklines section of the ribbon.

Sparkline charts on the ribbon

In the pop-up window, enter a range of cells or select it in the worksheet to populate the “Data Range” field. Confirm or change the entry in the Location Range field to place the sparkline. Click OK.

Vibrant Settings in Excel

You’ll then have a nifty little chart in a single cell to quickly show your data without the hassle.

2. Checking the data to exclude incorrect entries

When you create a worksheet to fill in or collaborate on a spreadsheet, you may end up with incorrect data in the wrong places. By using data validation, you can ensure that the information everyone enters is exactly what belongs.

RELATED: How to Limit Data Entry in Excel with Data Validation

Select the cell where you want to add validation. Click the Data tab and click the Data Validation button in the Data Tools section of the ribbon, or use the drop-down arrow to select this option.

Data validation on the Data tab

When the window opens, select the type of validation you want to use. You can select text, date, time, integer, decimal, list, or custom data type. Fill in the remaining fields according to the selected data type.

Setting up data validation

Optionally, you can use the Input Message and Error Alert tabs to customize the instruction or error message. Click “OK” when you’re done.

Going forward, when someone adds data to that cell, they will need to enter the data that Excel expects to validate. If they don’t, they will receive an error message.

Data validation error

The data validation feature is a great way to create dropdowns on your data entry sheet.

3. Status bar for viewing (and copying) calculations without formulas

You may want to do a quick calculation, such as adding or averaging several numbers, but you don’t have to add a formula to the worksheet. Using the status bar, you can quickly and easily see the calculations.

Select the cells containing the values ​​you want to calculate. Then look down at the status bar. You will see Sum, Average, Minimum, Maximum and Count.

Status bar calculations

To customize the calculations displayed in the status bar, right-click on the status bar and select the ones you want to tick next to.

Set up calculations in the status bar

For Windows users, you can also copy the value directly from the status bar. Just select the one you want and it will be placed on your clipboard. You can then paste it into your sheet or paste it into another application.

RELATED: How to copy values ​​from status bar in Microsoft Excel

4. Flash Fill to Fill Cells

If you have a lot of data to fill in, Flash Fill can help you do this by recognizing patterns. For example, you might have a list of first and last names in one column that you want to split into two columns. By entering just one piece of data, Flash Fill can fill in the rest.

Add some data to a cell. In our example, we will enter a name. Then go to the Data tab and click the Live Paint button in the Data Tools section of the ribbon.

Quick Fill in the Data tab

You should see Excel fill in the rest of the cells with these names. The same can be done with the last name. Type one into the top cell and click the Flash Fill button. Boom, one and done!

Data flash is full in Excel

5. Remove duplicates with one click

There are duplicates. This is especially true if you are importing data into Excel from another source. You may need time to clean up duplicate data. In Excel, you can remove duplicates in seconds.

Select the data you want to check for duplicates. Click the Data tab and click the Remove Duplicates button under the Data Tools section of the ribbon.

Remove Duplicates in Data Tab

In the window that opens, confirm the columns you want to check and click OK.

Columns for repeating data

You will then see your duplicates disappear with a message telling you how many have been removed.

Duplicate a deleted message

6. Formula Quick Results Viewer

If you have a large workbook with many sheets and tons of data, the Watch window will help you keep track of your formulas. Instead of looking for those changing results in your spreadsheets and data, just add a formula to the tool and quickly view the results.

RELATED: How to Use the Microsoft Excel Watch Window for Formulas

Select the cell containing the formula you want to view. Click the Formulas tab and select Viewport under the Audit Formulas section of the ribbon.

Tab view window

Click Add Clock to add the formula to the list.

Add Watch to Watch Window

Confirm that the correct cell is selected and click Add.

Add formula to viewport

Now you can open the Formula Viewer at any time to see the changing results of your formulas without having to hunt for them.

Viewer window with formula results highlighted

7. Analyze data easily

Microsoft Excel provides a handy feature to automatically analyze your data. The appropriately named “Data Analysis” feature can save you time when you need a quick analysis.

RELATED: How to Use the Data Analysis Function in Microsoft Excel

Select the sheet you want to work with and go to the Home tab. Click “Data Analysis” in the “Analyze” section of the ribbon.

Analyzing data on the Home tab

On the right, you’ll see an open sidebar that allows you to ask questions about your data, get insights, view frequencies and patterns, and insert items like charts and pivot tables.

Data Analysis Sidebar

Be sure to scroll the sidebar to the bottom and click the link to view all the available analysis results for your data.

Additional Results for Data Analysis

I hope you start using these Excel features to save time, reduce errors, and keep up with ever-changing data. If you want to continue exploring the power of Excel, be sure to check out our guide to the essential Excel features everyone should know.


#Handy #Microsoft #Excel #Features #Missed

About the author

admin

Leave a Comment