#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.
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:
- Select a cell that contains an important and frequently visited piece of information.
- Click in the Name Box (this will color the currently displayed address blue).
- Delete (or backspace) the current information.
- Type in a name for your bookmark.
- 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.
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.
This will provide a dialog box where you can add, modify, or delete named ranges.
Featured Course
Excel Essentials for the Real World
#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.
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.
This opens the Analyze Data panel where several suggested reports are presented for your review.
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.
If you are satisfied with the result, click the Insert PivotTable button to create the Pivot Table on a new sheet.
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
#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.
The Forecast Sheet tool was introduced in Excel 2016 and resides in the Forecast group of the 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.
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.
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
#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.
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.
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.
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
#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”.
From here, you can create a query to import data from dozens of different data sources.
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
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.
