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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
In the window that opens, confirm the columns you want to check and click OK.
You will then see your duplicates disappear with a message telling you how many have been removed.
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.
Click Add Clock to add the formula to the list.
Confirm that the correct cell is selected and click Add.
Now you can open the Formula Viewer at any time to see the changing results of your formulas without having to hunt for them.
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.
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.
Be sure to scroll the sidebar to the bottom and click the link to view all the available analysis results for your data.
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