3 Excel Features you Might Be Missing Out On

Excel is one of those applications that can do so much that people often wonder where to begin. Like anything else, without a practical use for the application, it can be challenging to find it a worthy investment of your time. I have already written several posts covering various topics about Excel. If you are interested in learning more about Excel in general, please visit our technology blog index and search for "Excel".

This post discusses three features in Excel that you could be missing out on, how to use them and why they are helpful.

Additionally, below is a video discussing these features and how to use them.

3 Excel Features you Might Be Missing Out On

The three Excel features we are going to cover in this post are:

1. Advanced search - searching the entire contents of a workbook versus a sheet
2. Data views - using filters to view specific groups of data
3. Comprehensive functions - using data from multiple sheets of a workbook in a function

First it is important to understand the difference between a sheet and a workbook in Excel. A sheet is a single tab, whereas all of the sheets in a single file make up the workbook. A new Excel file begins with a single sheet, but additional sheets can be added and removed as desired.

Advanced search

If you use Excel to track anything, search is your friend. You might be tracking purchases made each year for tax purposes, items you have sold, items you have created, employee hours, or a myriad of other things. The more information you track, the more it makes sense to break up the data into multiple sheets. Doing this helps keep the data in smaller, more easily viewed chunks.

Once you have multiple sheets in a workbook, looking for a specific piece of data can be cumbersome. If you aren't sure which sheet the data was entered into, you might use search multiple times. Luckily, searching through the entire workbook instead of the current sheet is easy and a much better way to search.

• In Excel, press the control + F keys to open the Find & Replace box.

• Click the "Options" button to change the search parameters. The default search options are to search only within the current sheet.
• To search the entire workbook, click the drop down next to "Within" and select "Workbook".
• Lastly, enter the information you are searching for in the box next to "Find what:".

• Click "Find All" or "Find Next" to search the workbook.
• If the information is found, search will move to the corresponding sheet and highlight the box with the result.

Data views

Another helpful thing you can do in Excel is add filters so you selectively look at only the data that meets the criteria for any column with a filter. This is a great way to get a better view of any specific type of data you are tracking. For instance, if you were tracking employee hours, you could filter the employee column, then select a specific employee so you would see only their hours.

Sorting the data by employee would give you similar information, but it moves the data around and all of the data is still in the viewable area. Using a filter is great because while the data remains unchanged, you see only what meets the filter criteria.

To create a filter:

• In Excel, right-click on the column to filter.
• Click on "Filter" in the menu, then select "Filter by Selected Cell Value".

• Once the filter has been added, the sheet will appear to be blank. This is because the filter is showing only those items that meet the selected criteria, and no criteria has been selected.
• Click the filter icon in the column where you just created it.

• Select the desired filter(s) and click "OK". The results will immediately appear.

• To view all of the data, check the boxes next to each item in the filter list, or remove the filter.
• To remove the filter, click the Filter button in the Data tab.

Comprehensive functions

As was mentioned earlier, to get a better overview of your data it is often a good idea to spread the data across different sheets of a workbook. Luckily, doing this does not hinder your data as Excel can easily handle functions with references to other sheets in the workbook. For instance, if you track something monthly or annually, you can still have sums, averages and many other summaries of data in multiple sheets in a single function.

To enter a function that references cells from multiple sheets:

• In a sheet, click on a cell to enter a formula.
• Begin entering the formula and select cells in the current sheet that are part of the function. If you are unsure of how to use functions, start by clicking on the Greek E which lists some common functions like sum, average, and more.
• In my example, I will create an average of a number over a 12-month period.

• Because I am calculating an average, I need to enter a comma after clicking on a cell in the current sheet to add to the function. If you were adding, you would add a +, to subtract a -, etc.
• Click on the tab of another sheet with data you want to include in the formula.
• Click on the cell to add it to the function.
• Continue adding any necessary punctuation, then clicking on tabs and selecting cells until all cells are included.
• Press enter to finish the function.

As you can see in the example above, the number in the cell is being calculated by the formula shown in the top bar. You can also see that any cell in a sheet other than the one where the function is will have the sheet name in front of the cell. This will vary based on what you have named your sheets, but the format will always be the same: SheetName!CellNumber.

Excel can be a very helpful tool as there are so many valuable ways it helps track and organize information. Understanding how to do an advanced search that will include information from all sheets, how to filter the data view and how to create comprehensive functions that include information from multiple sheets, are three features you might have been missing out on.

As always, the more you use and understand some of the features in Excel, the more valuable and efficient a tool it will become!