Hidden Excel Features That Will Save You Hours Every Week

Excel is a powerful tool that millions of professionals use every week to organize data, perform calculations, and create visualizations, but many users only scratch the surface of its capabilities. As a Financial Planning and Analysis Manager at Book+Street, a consulting firm that helps startups and small businesses with back-office functions, I work heavily in Excel daily for financial projections and budget analysis. I also teach Excel classes through Denison Edge, and I want to share some of my favorite Excel shortcuts for professionals that aren’t typically covered in basic courses.

Wildcards in Formulas

Ever needed to sum up values that share a common word or phrase in their descriptions? Wildcards are incredibly useful when trying to extract information from text without manually categorizing each item.

For example, if you run a restaurant and want to quickly calculate all sales for chicken-based menu items (chicken sandwich, chicken salad, chicken nuggets), wildcards can do this instantly without requiring you to create a separate category column.

What’s nice is if my data already has the information I want to pull out of it, I don’t need to go through and add a column. I can do a SUMIF, have it look at this range, and then use this wildcard, this asterisk, to say ‘Anything that has this in the name, pull out and just sum from it.’

Using the asterisk (*) wildcard in a SUMIF formula allows you to look for partial matches within text. This saves tremendous time compared to either manually summing items or creating additional categorization columns, especially when working with hundreds or thousands of data points.

Freeze Panes

Nothing is more frustrating than scrolling through a large dataset and losing track of which row or column you’re viewing. The Freeze Panes feature solves this problem by keeping important headers visible no matter how far you scroll.

To use this feature, select the cell below and to the right of where you want the freeze to occur, then go to View → Freeze Panes. Excel will lock everything above and to the left of your selection, keeping those reference points visible as you navigate through your data.

This simple tool eliminates constant scrolling back and forth to check column or row labels, making data analysis much more efficient and reducing errors from misaligned references.

Solver Add-in

For solving complex equations with constraints, the Solver add-in is invaluable. While not enabled by default, you can quickly add it through Excel’s Analysis ToolPak.

Solver helps you find optimal solutions when you have specific targets and variables. For instance, if you need to determine how many customers you need to reach a certain sales goal, Solver can calculate this instantly. Even better, you can add constraints like maximum capacity, available hours, or other limiting factors.

This tool shines when dealing with resource allocation problems. Imagine having a million-dollar budget to split between two projects—Solver can determine the optimal allocation to maximize your total return based on different project performance metrics.

Find and Replace

While this feature might seem basic, its power in handling large datasets is often underappreciated. Find and Replace can make sweeping changes across your spreadsheet in seconds.

Beyond simple text replacements, this tool is particularly useful for updating formula references. If you’ve created a complex spreadsheet with formulas referencing column G, but realize the data should come from column H instead, you can use Find and Replace to update all instances at once rather than tediously editing each formula.

Group Columns and Rows

Instead of hiding columns or rows that contain supplementary information (which can be easy to forget about), I recommend using Excel’s grouping feature to create collapsible sections.

Located in the Data tab, grouping creates a visual indicator with plus/minus buttons that makes it obvious when content is hidden and provides an easy way to expand or collapse sections. This is especially helpful when sharing files with colleagues who might need access to the additional data but shouldn’t be distracted by it initially.

Concatenate and Unique Functions

When working with names, IDs, or other text data split across multiple cells, the Concatenate function makes combining text effortless. For example, joining first and last names into a full name field takes just one formula.

Paired with the Unique function, users can quickly identify distinct entries in a dataset. This combination is particularly valuable for customer data analysis—after concatenating names, you can use Unique to generate a list of individual customers from transaction data containing multiple purchases by the same person.

Data Validation

Prevent data entry errors and maintain consistency by implementing Data Validation rules. This powerful feature allows you to create dropdown lists, restrict inputs to specific ranges or formats, and ensure data integrity.

Data Validation is especially useful when sharing spreadsheets with colleagues who need to input information. By creating dropdown lists linked to master data sources, you eliminate typos and format inconsistencies that might break your formulas later.

The better data that you put into an Excel spreadsheet, the better data you’re going to get out–and the less time you’re going to have to spend fidgeting with formulas to get the information that you want.

Sheet and Workbook Protection

For sensitive data or complex models where formula integrity is crucial, Excel offers both sheet-level and workbook-level protection options that help preserve your data, saving you time from having to go back in and fix things should someone else accidentally delete or alter cells.

Sheet protection allows you to lock specific elements while keeping others editable—perfect when you want colleagues to input data but not modify formulas or formatting. Workbook protection secures the entire file, requiring a password to access any of its contents.

Learn More Excel Time-Saving Tips

Want to master these and other advanced Excel functions? I’ll be teaching Excel classes this summer through Denison Edge. You’ll gain hands-on experience with these time-saving techniques and take your spreadsheet skills to the professional level. Remember, the most significant productivity gains often come not from knowing more formulas but from using Excel’s built-in features in smarter ways.