#1 – Using Bookmarks to Navigate Excel

We’ve all become reliant on using bookmarks in our favorite web browsers.  Having the ability to instantly navigate to a website without being burdened with remembering the exact web address.  Admittedly, some website addresses are impossible to remember.

Excel has the same capability.  With a single click of the mouse, we can be transported to our most important workbook locations.

In full disclosure, Excel doesn’t have an actual “bookmark” feature, but we can repurpose an existing feature to serve the same need.  That feature is the Name Box.

Excel - Pointing to Name Box

Located in the upper-left corner of the worksheet, the Name Box provides many useful features, such as:

  • Display the address of the currently selected cell
  • Navigate to a cell location
  • Navigate to an official Excel table
  • Highlight an entire column or row of cells
  • Highlight a range of cells
  • Assign names to single cells or ranges of cells
  • Provide instant selection of named single cells or cell ranges

It’s those last two bullets that are going to allow us to ‘bookmark’ important locations in the workbook.

The bookmarking process is simple:

  1. Select a cell that contains an important and frequently visited piece of information.
  2. Click in the Name Box (this will color the currently displayed address blue).
  3. Delete (or backspace) the current information.
  4. Type in a name for your bookmark.
  5. Press Enter.

That last step, pressing Enter, is very important.  Many beginners make the rookie mistake of clicking a different cell or clicking a button on the ribbon after performing the 4th step.  If you don’t hit the Enter key, the entry will be discarded.

There are a few naming restrictions:

  • No two cells (or ranges) can have the same name
  • The name must start with a letter (but it can contain numbers within)
  • Spaces are not allowed in a name (but you could use an underscore character to simulate a space)

To use a bookmark, click the small down arrowhead at the right of the Name Box to display a list of named ranges.

Excel - Pointing to Name Box dropdown button
Excel - Name Box dropdown list

TIP: It’s good practice to use a consistent prefix with your bookmarks, like ‘Book’ or ‘bm’.

If you need to manage your ‘bookmarks’, select Formulas (tab) – Defined Names (group) – Name Manager.

Excel - Name Manager button on Formulas ribbon

This will provide a dialog box where you can add, modify, or delete named ranges.

Excel - Name Manager dialog box

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

#2 – Analyze Data for Instant Ideas

This next time-saver was previously advertised as “Ideas” but has been recently rebranded as “Analyze Data”.

This feature is available for Microsoft 365 users, and it leverages Microsoft’s A.I. functionality.

Suppose you just received the following dataset.

Sample Excel data set

You have sales covering 12 months, and you want to create a report that displays the 5 lowest rated products over the entire period.

This report can be done in 2 clicks.

The Analyze Data feature is located on the far right of the Home ribbon.

Excel - Analyze Data button on Home ribbon

This opens the Analyze Data panel where several suggested reports are presented for your review.

Excel - Analyze Data suggested pivot table reports

Since our question is fairly specific, we can type our question into the “Ask a question about your data” field at the top of the panel.

Excel - Analyze Data ask a question interface
Excel - Analyze Data A.I. output based on custom question

If you are satisfied with the result, click the Insert PivotTable button to create the Pivot Table on a new sheet.

Excel - Analyze Data produced output

The output is a standard Pivot Table, so you can customize this using your existing Pivot Table skills.

If you want to learn more about this feature, check out this post.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

#3 – One-Click Forecasting

Forecasting is a must-have skill when working with sales or revenue data.

Below, we have sales information for a several-year period.

Excel - Sample data set

The Forecast Sheet tool was introduced in Excel 2016 and resides in the Forecast group of the Data ribbon.

Excel - Forecast Sheet button on Data ribbon

To use the Forecast Sheet tool, select the range of cells that contains the dates and historical sales (CTRL-A), then click the Forecast Sheet button.

Excel - Create Forecast Worksheet dialog box

The future sales are forecasted, accounting for seasonal behavior.  We also see lines for the upper and lower bounds of expected behavior.

The end of the forecasting period can be adjusted along with other behaviors, such as confidence level, seasonality, and handling of duplicate data.

Excel - Create Forecast Worksheet dialog box control options

I have a separate post that details the behaviors and uses of the Forecast Sheet feature.  Check it out for a comprehensive examination of this incredibly useful tool.

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

#4 – Excel’s Quick Analysis Tool

This tool is like having your very own Excel assistant.

Excel’s Quick Analysis tool gives you the power to format, chart, and calculate complex data set scenarios with a single click.

With your cursor in the data range, press CTRL-A to highlight the data, then press CTRL-Q to activate the Quick Analysis tool.

Excel - Quick Analysis feature

From here, you can perform the following tasks with a click:

  • Formatting – Apply conditional formatting based on Top/Bottom rules or percentages, data bars, color scales, icon sets, etc.
  • Charts – Create a variety of charts based on data-based analysis.
  • Totals – Create totals, averages, or counts in rows and columns.
  • Tables – Create Excel Data Tables or Pivot Tables.
  • Sparklines – Add sparklines in cells (mini cell-based charts) that display lines, columns, or win/loss information.

Any of these options can be previewed by just hovering the mouse pointer over the option.

Excel - Quick Analysis feature using icons to indicate status

Because the results are products of existing Excel features (ex: Conditional Formatting, charts, formulas, Pivot Tables, etc.), any results can be modified and customized using your existing skills in those areas.

Quick Analysis features can be layered to create visually exciting reports that most viewers will think you spent hours crafting.

Excel - Quick Analysis features used together

If you’re curious to learn more about Sparklines, check out this video where I show how you can format them for greatest impact.

Featured Course

Automate With Power Query – Recipes to solve business data challenges

Do you want to become more confident using Power Query and automate your entire data workflow? Join this course and learn from real-world scenarios.
Learn More

#5 – Power Query (The Ultimate Time-Saver)

Power Query is NOT a tool reserved for Excel elites or uber-geeks.  Power Query can be used by anyone who works with data: data in any format, data used for any purpose.

If you ever need to bring data into Excel from outside sources (e.g., delimited text files, other Excel files, PDFs, databases, websites, folders, etc.), then fix or clean up the data, Power Query is the ultimate time-saver tool.

Power Query is located on the Data tab at the far left in a group called “Get & Transform”.

Excel - Power Query starter controls on Data ribbon

From here, you can create a query to import data from dozens of different data sources.

Excel - Power Query list of data connectors

NOTE: If you use Power Query in Power BI, the list of connectors grows to just under 200.

Power Query can perform an astounding number of actions in seconds that would require years of study and practice using traditional Excel tools.  Actions such as:

  • Creating Pivot Tables
  • Stacking tables
  • Merging tables
  • Performing complex lookup operations
  • Splitting data
  • Concatenating data
  • Writing aggregation formulas
  • Grouping data
  • Unpivoting data
  • Adjusting dates

The list of potential actions would take 10 more blog posts just to list them all.

And the most amazing part of Power Query is its reusability.

Power Query records every transformation you perform on a data source and turns it into repeatable code.

Now don’t worry; you never have to look at the code if you don’t want to, but it’s that code that will allow you to click a button whenever your data changes, and process the new data in seconds just as you did when you originally cleaned it up.

Define once, consume anytime.  It’s really exciting to see it in action.

I have several videos you can check out showcasing real-world uses of Power Query.  You can see for yourself the hours, days, or even weeks’ worth of work that can be performed in seconds. Here’s the full playlist.

If you’d like to learn Power Query in a structured, methodical way, we have a class called “Master Excel Power Query – Beginner to Pro”.

Featured Course

Master Excel Power Query – Beginner to Pro

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.
Learn More
Power Query Course cover

Once you start using Power Query, you will not know how you could live without it in your bag of tricks.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.