Excel-super-guide_features of microsoft excel

excel tips

1. THE TOPMOST USEFUL
EXCEL
FEATURES

2. ABOUT THIS GUIDE
It’s widely accepted that there are over 1 billion users of Microsoft Excel, yet the average user is not familiar with even half
of the top 50 features listed in this guide! While most people picture the typical Excel user as an accountant or financial
analyst building a report or model, Excel usage is much wider across business functions of all kinds. Most of the features in
this guide are useful to any Excel user, but the more hours in your day you use Excel, the more you’ll find this guide helpful.
To focus on the top 50 Excel features, we reviewed dozens of resources across the internet, as well as our own experience
with features new and old that we felt were important to learn. Your top 50 features could be different and that’s okay!
How this guide was built
This guide was designed for three reasons: 1) test your Excel knowledge, 2) be a resource you can refer to often, and 3) save
you a lot of time by exposing you to efficiency features that are native to Excel and that you can install via an add-in.
Why use this guide
We offer training on Excel, productivity, leadership, and other valuable topics in a corporate and public cohort setting.
To learn more, visit our training page at https://excelevate.co/wp/home/training/
How to learn more
Background

3. HOW THIS GUIDE WORKS
This guide contains 50 Excel features that every Excel user should be aware of. They are categorized and rated by utility,
complexity, and estimated time to learn. Common keyboard shortcuts are also included. Each feature has a fairly thorough
description to help you understand what it is. Bookmark the ones you want to learn more about and use this as a handy
reference guide when you’re looking to accomplish something in Excel that you might not be as familiar with.
Many Excel features have gotchas that are frustrating to learn by trial and error.
Common gotchas are listed for each Excel feature here in the gotchas section, but there could be others not listed.
Gotchas
There are often many tips for Excel features that are lesser-used approaches or best practices.
These tips are listed for each Excel feature here in the tips section, but there could be others not listed.
Tips
Macros in the XLEV8 add-in related to the Excel feature are listed here, along with a link to the related how-to page.
To learn more, visit the XLEV8 add-in page at https://excelevate.co/wp/home/xlev8-add-in/
XLEV8 Add-in Macros
Contents
Description

4. HOW THIS GUIDE WORKS (CONT.)
Icon definitions
Utility (scale of 1-100) – this is a general sense of the feature’s usefulness. The higher the rating, the more
likely it is to be used and the more you should consider familiarizing yourself with it.
Complexity (scale of 1-5) – this is a general sense of how complex the feature is for the general user. The
higher the rating, the more complex it is and the more difficult it might be to learn and master.
Time to learn (minutes) – this is an estimate of the time it would take a general user to learn the feature at
an intermediate level.
Category – this is the (color-coded) category the feature best fits in. It might be helpful to review other
features in the same category. Note that some features can fit in multiple categories.
Keyboard Shortcut(s) – this denotes the most relevant keyboard shortcut(s) for the feature. Note that there
could be multiple keyboard shortcuts for the same feature, especially when using the Alt key approach.

5. INDEX
01. Structure
02. Ribbon / Quick Access Toolbar
03. Navigating
04. Cell Referencing
05. Keyboard Shortcuts
06. Macros
07. Filtering
08. Sorting
09. Slicers
10. Copy and Paste
11. Find and Replace
12. PivotTables
13. PivotCharts
14. Tables
15. Charts
16. Sparklinks
17. Colors
18. Number Formats
19. Other Cell Formats
20. Format Painter
21. Cell Styles
22. Conditional Formatting
23. Data Validation
24. Named Ranges
25. Math Functions
26. Date and Time Functions
27. Logical Functions
28. Lookup and Reference Functions
29. Text Functions
30. Other Functions
31. Formula Auditing
32. Editing and Breaking Links
33. Integrations
34. Power Query
35. Data Tools
36. Get Data From Picture / PDF
37. Freeze Panes
38. Hide/Unhide, Group/Ungroup
39. Clipboard Pane
40. Comments and Notes
41. Shapes and Pictures
42. Status Bar
43. New Window
44. Side by Side View
45. Window View
46. Print Settings
47. Undo/Redo
48. Show Changes
49. Sheet Views
50. Data Protection

6. 01. STRUCTURE
Understanding the structure of the Excel application and Excel files is basic but important. Excel files are known as
workbooks (or “books”). Workbooks contain worksheets (or “sheets”), grids of columns (letters) and rows (numbers), which
intersect at cells. A cell range consists of cells, rows, or columns. Worksheets can also contain shapes, pictures, charts, and
form elements overlaying cells. Cells contain formulas or directly-entered values and can be extensively formatted.
Description
Inserting cells, rows, or columns can throw off some formulas that reference the cells being shifted by insertion.
Deleting cells, rows, or columns can cause formula errors for any other cells that reference the deleted ones.
Gotchas
Consistency is key for organizing Excel files, especially the way you organize data and the formats you use.
Make sure to bookmark this guide and any other helpful resources you find to quickly access them in a pinch!
Tips
Search Recent Files – easily search for files by path or file name using auto-complete as you type.
Delete Picker – apply common delete actions quickly with shortcuts and defaults.
XLEV8 Add-in Macros
100 1/5 30 min Structure (menu) : mimic right-click
NEXT INDEX

7. 02. RIBBON / QUICK ACCESS TOOLBAR
In 2007, Microsoft made a major menu change when it introduced the ribbon UI, with a mix of icons/sizes, menus, sections,
and tabs, based on how users tend to interact with them. The ribbon can be collapsed down to the main tabs if desired.
Ribbon menus can be customized to user preferences (including new tabs and groups). Commonly-used commands can be
added to the Quick Access Toolbar (or “QAT”). Customizations made to the ribbon and QAT can be imported and exported.
Description
The ribbon can become collapsed by clicking the arrow on the bottom-ride side (click a tab to display the full ribbon).
Ribbon tabs can accidentally be hidden by customizing the ribbon (right-click the ribbon anywhere to change this).
Gotchas
To add a command to the QAT or ribbon, right-click on it and either select Add to QAT or Customize the ribbon.
The first 10 QAT commands can be run via keyboard shortcut (Alt+1, Alt+2, etc.) in Excel and other apps.
Tips
Search Commands – easily search for common Excel commands using auto-complete as you type.
Command Picker – apply common Excel commands quickly with shortcuts and defaults.
XLEV8 Add-in Macros
90 2/5 30 min Efficiency Alt-1(:10) : trigger QAT buttons
NEXT  BACK INDEX

8. 03. NAVIGATING
Navigating workbooks and worksheets is very frequent for Excel users. Learning to do this efficiently can save a great deal of
time. Four tools can help here: the Navigation pane, hyperlinks, the name range box, and the go-to menu. The navigation
pane lists sheets, tables, used ranges, and shapes. Hyperlinks to a specific sheet/cell can be inserted to help navigate. The
name range box lists named ranges to jump to. The go-to box provides basic and advanced options for navigating.
Description
Cells with formatting but no values can often throw off navigation commands.
If cell navigation seems stuck, try toggling the scroll lock, or switching to another Excel window or another app then back.
Gotchas
Leverage keyboard shortcuts as much as possible for navigation, especially in large workbooks with many sheets/rows.
Include a table of contents within your workbook to speed up navigation and help organize it for you and others.
Tips
Sheet Action Picker – apply common/custom sheet commands and search sheets quickly with shortcuts and defaults.
Select Picker – leverage several shortcuts and defaults to select items quickly.
XLEV8 Add-in Macros
95 2/5 15 min Efficiency Alt-w-k : show navigation menu
NEXT  BACK INDEX

9. 04. CELL REFERENCING
The ability to link cells together via referencing is one of the most powerful features of Excel. Imagine having dozens of
worksheets full of insightful data points that update instantly based on a few key inputs. Cell references are included in
formulas with a variety of math and function types. Cell references can be relative or absolute, which mainly relates to how
they are copied – absolute ($ symbols before column/row) stay the same, relative change according to the copy/paste offset.
Description
Getting the absolute and relative references just right can be tricky – give it some practice and thorough review.
Cells cannot refer to themselves (this is called a circular reference), and Excel will warn you when you create them.
Gotchas
Press F4 to toggle the reference type when a cell reference is selected in the formula bar.
Use the INDIRECT function to refer to cells dynamically – building the file/path, sheet, column, and row from cell values.
Tips
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros
100 3/5 30 min Formulas and functionsCtrl+[ : jump to first reference
NEXT  BACK INDEX

10. 05. KEYBOARD SHORTCUTS
Learning a moderate to high number of keyboard shortcuts makes a huge difference in productivity, both in Excel, as well as
any other applications you use frequently. Numerous studies show that keyboard shortcuts are over 3x faster than using the
mouse for three main reasons: 1) physically moving your hand to the mouse and back takes time, 2) icons are so small it’s
easy to click the wrong one (keys are more forgiving), and 3) mouse commands are often buried in multiple menu clicks.
Description
For commands with no default shortcut, use the Alt sequence to mimic left-clicks and the menu key to mimic right-clicks.
Try not to override useful default shortcuts if assigning macros or other commands to your own keyboard shortcuts.
Gotchas
Hover over ribbon commands to help learn shortcuts for commands you use frequently (or press Alt to follow along).
Macros can help you combine common steps, which can then be assigned a keyboard shortcut.
Tips
Search Macros – find/run macro with auto-complete, set/unset keyboard shortcut, and set/unset favorites.
Assign Macro Shortcuts – set add-in macro shortcuts in one bulk step, and print out assigned shortcuts for reference.
XLEV8 Add-in Macros
100 2/5 30 min Efficiency Alt-… : shortcuts to virtually everything
NEXT  BACK INDEX

11. 06. MACROS
Macros are a polarizing topic in Excel. Some people love them, some people hate them, and most don’t realize they’re an
option. Virtually every aspect of Excel is exposed to macros, making the automation potential limited only to your
imagination. Two great uses of macros are 1) automating repetitive, time-consuming tasks and 2) providing shortcuts that
combine steps. You can build macros by recording your steps or by writing the code itself (or copying from existing code).
Description
The effects of macros generally cannot be undone. Save often and before running a macro that automates many steps!
Build and use extensive validation with macros to prevent making a big mess much quicker than you would manually.
Gotchas
Macros can be stored in workbooks, your personal macro library, or add-ins. The latter two make them always available.
Macros can usually be assigned to specific keyboard shortcuts, making them even easier and quicker to use.
Tips
Search Macros – find/run macro with auto-complete, set/unset keyboard shortcut, and set/unset favorites.
Repeat Macro – quickly re-run one of the last ten run macros (handy for those without keyboard shortcuts).
XLEV8 Add-in Macros
100 4/5 240 min Efficiency Alt+F8 : show macros to run
NEXT  BACK INDEX

12. 07. FILTERING
Filtering is one of the most useful features of Excel for data analysis, assuming your data is structured nicely in a tabular
format. There are many filtering options – text, numbers, dates, and even fill colors. One of the most common and basic
ways to use filter is searching by text through the column header drop-down menu after filters are applied, showing you
results as you type. Filtering can get much more advanced, with several different criteria used across columns.
Description
When filters are applied, rows are hidden, but can sometimes still be changed (use select visible cells to prevent this).
Merged cells (almost always bad!), inconsistent cell formats, and selecting multiple workbooks can cause filter issues.
Gotchas
Filter Picker – apply several helpful filter actions with shortcuts and defaults, including favorite filter/sort settings.
Bulk Filter – apply filter settings across multiple sheets in one step.
XLEV8 Add-in Macros
Press Alt+↓ with a filter column heading selected to show the filter field box.
When searching text, use * as a wildcard for an unknown number of characters and ? for just one character.
Tips
100 2/5 30 min Data analysis Alt-h-s-f : apply filter to range
NEXT  BACK INDEX

13. 08. SORTING
Sorting is another useful feature of Excel for data analysis and presentation, assuming your data is structured nicely in a
tabular format. There are many sorting options – text, numbers, dates, and even fill/font colors. Sorting can be applied
ascending or descending across one or more columns, or even horizontally across columns! A lesser-known approach is to
sort by custom lists, such as a list of weekdays.
Description
A mix of text and numbers could return unexpected sorting results.
Make sure to select the entire data set to be sorted and check whether or not the data set contains headers.
Gotchas
Sort Picker – apply several helpful sort actions with shortcuts and defaults, including favorite sort settings.
Sort Ranges – apply sort settings across multiple sheets and ranges in one step.
XLEV8 Add-in Macros
Consider providing an icon or some indication of how data is sorted in your table to aid users.
To be able to return a sorted set back to its original order, consider adding a sort helper column.
Tips
100 2/5 30 min Data analysis Alt-h-s-u : apply custom sort to range
NEXT  BACK INDEX

14. 09. SLICERS
Slicers are a helpful feature to visually show how tabular data is filtered. Since it’s not always obvious how data is filtered,
slicers provide great visual cues. It’s very common to see slicers used in dashboards to provide a quick way to filter data by
day, category, region, etc. They work best with fields/columns that do not have unique values, rather around 30 or less so
that all the unique values can be displayed in the slicer box.
Description
To apply slicers, your data must be in a table or PivotTable.
If your data set is not responding to a slicer change, review the slicer settings for whether items with no data are hidden.
Gotchas
Filter Picker (option L) – quickly insert a slicer on the selected column (also coverts data set to table if needed).
XLEV8 Add-in Macros
Use the slicer options ribbon/menu to increase the number of columns of buttons displayed in the slicer if desired.
Slicers can be applied to advanced filters if you prefer to filter your data outside of the original data set.
Tips
70 2/5 15 min Data analysis Alt-n-s-f : add slicer
NEXT  BACK INDEX

15. 10. COPY AND PASTE
Copy and paste is one of the most fundamental efficiency tools in Excel and other applications. It’s a great way to leverage
your previous efforts (or those of other people) and not reinvent the wheel. Though the most common actions involve
copying and pasting everything from cells, columns, or rows, Excel lets you copy and paste in a variety of ways – raw text,
formats, values, formulas, comments, and more.
Description
Don’t just blindly paste everything you copy – paste only what you need to when pasting to areas with existing formats.
If you’re pasting large amounts of data, Excel can freeze and crash. Make sure to save before pasting large data sets!
Gotchas
To paste very specific cell attributes or apply special actions, use the paste special menu (or use the macro below!).
While often helpful, the Paste Options popup can be annoying. Turn it off in the File > Options > Advanced section.
Tips
Copy Special Picker – apply expanded copy types like formats, sum of selected cells, raw text, etc.
Paste Special Picker – apply expanded paste types like formats, formulas, column widths, links, etc.
XLEV8 Add-in Macros
100 1/5 15 min Efficiency Ctrl+c/Ctrl+v : copy/paste contents
NEXT  BACK INDEX

16. 11. FIND AND REPLACE
Find and replace is another great way to make bulk changes quickly and systematically in Excel and many other applications.
Updating names, dates, and other data points that change over time or between versions is very common, and leveraging
find and replace is a great way to do it. Excel offers several advanced options for find and replace, such as searching
formats, case-sensitive strings, and searching within formulas or values.
Description
If multiple cells are selected, find/replace applies to just those cells. Selecting just one cell applies to all cells.
Be wary of accidentally replacing unintended values (such as parts of a word, i.e. “ball” in “baseball”).
Gotchas
Excel lets you find and replace using specific formats – this can be really powerful if cleaning up stray formats.
Searching by rows or columns can have a huge difference in run time when searching large data sets.
Tips
Find Replace Picker – apply configurable favorite find/replace sets that you perform repetitively.
Bulk Find Replace – apply several find/replace sets in specific sheets/ranges in one bulk step.
XLEV8 Add-in Macros
90 1/5 15 min Efficiency Ctrl+f/Ctrl+h : find/replace text/formats
NEXT  BACK INDEX

17. 12. PIVOTTABLES
PivotTables are extremely popular with Excel users who routinely perform data analysis. The ability to quickly and easily
aggregate, compare, and slice-and-dice your data makes PivotTables really useful. Most people just use PivotTables at a
basic level, summarizing data by location, month, category, etc., but within the settings, there are many more options for
displaying and working with PivotTables, such as filtering, sorting, slicers, timelines, auto-grouping, and calculated fields.
Description
PivotTables do not automatically refresh as data changes or expands, unless the source data is in a table.
Number formats in PivotTables do not automatically follow the source data – use the format painter to quickly match it.

Gotchas
Pivot Picker – apply several helpful pivot actions with shortcuts and defaults, including toggling display settings.
Unpivot Table – easily convert a pivoted structure (wide across several columns) to unpivoted (tall across rows).
XLEV8 Add-in Macros
Consider changing the PivotTable structure to tabular view if you want to add formulas or use it as a table.
Try using the Recommended PivotTables feature – it shows several common views of your data you may want to use.
Tips
90 3/5 120 min Data analysis Alt-n-v : insert pivot table
NEXT  BACK INDEX

18. 13. PIVOTCHARTS
PivotCharts are an extension of PivotTables that add visualization. Many users feel they are they easiest way to work with
charts in Excel since the data structure is automatically set up and it’s easy to drop fields into the various areas (filter, series,
category, value), as well as move them around to change the structure of the chart, or even stack fields together. Changes to
the structure of a PivotTable will automatically update the linked PivotChart structure and vice versa.
Description
Not all chart types are available for PivotCharts, but most basic and common chart types will work.
If data is not showing up as expected, review the PivotChart filters and ensure all source data is included.
Gotchas
Pivot Picker – apply several helpful pivot actions with shortcuts and defaults, including toggling display settings.
XLEV8 Add-in Macros
Dual-axis charts work well with PivotCharts (i.e. bar chart for one field and a line chart for a second field)
To show relativity or trends, consider sorting data best to worst and show period-over-period comparisons.
Tips
65 3/5 60 min Presentation Alt-n-s-z-c : insert pivot chart
NEXT  BACK INDEX

19. 14. TABLES
Tables are a great feature in Excel for automatically applying formatting, updating the range used for items referring to the
table (such as charts, PivotTables, and PivotCharts), and automating data filled in. There are many pre-defined formats and
styles that can be applied to tables, such as banded rows or columns. One of the most helpful features of tables is that they
automatically expand (formatting and formulas). Some Excel features, such as slicers, require data to be set up as a table.
Description
Ensure that you check whether your table has header rows and total rows so they are treated correctly.
Ensure that your data range does not have any blank columns or rows when designating it as a table.
Gotchas
Insert Picker (option T) – quickly insert a table based on the selected range.
XLEV8 Add-in Macros
Formulas in tables can use normal column/row references or the easier-to-read table column name format.
When you update the formula in one table row, the formulas in all rows automatically update.
Tips
75 2/5 30 min Data analysis Ctrl+t : insert table
NEXT  BACK INDEX

20. 15. CHARTS
Charts are awesome for helping users understand lots of data. Although there are over 20 types of charts available in Excel,
the most common charts are column, bar, line, pie, and scatter. Charts have a large array of format and display settings that
can be configured to your liking so that they are very helpful to your audience. With all the options available though, it’s
best to keep it simple wherever possible and remove anything redundant or unnecessary.
Description
Unless linked to a table as the data source, charts do not automatically expand as data is added.
Be careful setting the axis min/max values so that your chart conveys the appropriate message.
Gotchas
Add a helpful title to your chart – not just what it is but the biggest takeaway – you can even link this to a cell value.
Consider formatting the data series with colors that vary by data point to add some polish to the chart.
Tips
None yet
XLEV8 Add-in Macros
75 4/5 120 min Presentation Alt-F11 : insert chart on sheet
NEXT  BACK INDEX

21. 16. SPARKLINES
Sparklines are a helpful way to add a modest bit of visualization to a large data set. They are generally used with data across
columns, and they are especially helpful with trended data over time. There are only three types – line, column, and
win/loss. If more than 12 data points are displayed, generally the line type is recommended. To quickly denote positive and
negative values, use the win/loss type.
Description
Sparklines can be hard to read if cell sizes are too small – consider making the cell heights and widths just a little bigger.
Empty/blank cells can be deceiving with sparklines. Consider adjusting the settings or use the line type.
Gotchas
Sparklines are usually presented just to the right of trended data, but can also be displayed just to the left as well.
Consider changing the settings for sparklines (such as the color) to spotlight high and low data points.
Tips
Insert Picker (option L) – quickly insert a sparkline to the right of the data based on the selected range.
XLEV8 Add-in Macros
40 2/5 20 min Presentation Alt-n-s-l : insert sparkline
NEXT  BACK INDEX

22. 17. COLORS
Colors are a major part of formatting in Excel. Between fill colors, font colors, border colors, and shape colors, they are used
quite extensively to liven up the data, make it easier to read, and make certain things stick out. There are some best practice
and norms (red = bad/negative, green = good/positive, yellow = not yet done), but feel free to use them in a way that makes
sense to you and your users. Consistency and simplicity is key. Consider defining a legend both within workbooks and SOPs.
Description
The themes you set within Windows and within Excel can affect the way colors display on your screen.
If colors are not changing as expected, review whether conditional formatting is applied and overriding the colors.
Gotchas
Consider using cell styles, which can save a variety of formats (i.e. colors) as easily updated styles across your workbook.
Consider using colors that can be understood by people who have different types of color-blindedness.
Tips
Toggle Cell Highlight – cycle through five definable cell fill colors and back to no fill color.
Cell Highlight Picker – set cell fill color based on a 1-5 number shortcut and assign your favorite default fill colors.
XLEV8 Add-in Macros
95 2/5 20 min Formatting Alt-h-h : show fill color palette
NEXT  BACK INDEX

23. 18. NUMBER FORMATS
Number formats are another major part of formatting in Excel, as spreadsheets are so commonly filled with numbers. Excel
offers a variety of formats to display numbers, dates, times, and text in very specific ways. Currency, decimals, thousand
commas, rounding, other symbols, and even colors can be displayed in an automated way if desired. Excel offers a dozen or
so pre-set common number formats, but custom formats are the ultimate option to display numbers very precisely.
Description
With few exceptions (i.e. %’s), the number format is just for display purposes; the raw number is still used for calculations.
Number format coding can be tricky - make sure to use resources with examples and code definitions carefully.
Gotchas
It’s generally helpful to use different number formats for different number types (currency, variance, percentage, etc.).
The TEXT function can be used to display formatted numbers within a formula in a text string.
Tips
Toggle Number Format – cycle through five definable number formats and back to the general format.
Number Format Picker – quickly apply common number formats and define your five custom number formats.
XLEV8 Add-in Macros
95 2/5 20 min Formatting Ctrl+1 : show number format menu
NEXT  BACK INDEX

24. 19. OTHER CELL FORMATS
Aside from colors and number formats, Excel offers several other cell formats, such as borders, alignment, indentation,
orientation, wrap text, font colors, font name, font style, and font sizes. Borders have three components: side(s), style, and
color. Alignment has two components: vertical and horizontal. Text orientation can be rotated 90° upwards or downwards.
There are dozens of fonts to choose from, and hundreds more can be imported and used.
Description
Formats may look okay on the screen, but not look so good when printed. Test it out with print preview.
If you merge cells, you’re asking for trouble. Use the center across selection option instead.
Gotchas
For all formatting types, consistency and simplicity helps your users understand the message in the easiest way.
For using borders to designate totals, consider using the top format with the total so new rows can easily be added.
Tips
Cell Action Picker – quickly apply common cell actions and set your favorite action as the default.
Format Picker – quickly apply common cell formats and set your favorite action as the default.
XLEV8 Add-in Macros
85 2/5 20 min Formatting Ctrl+1 : show formatting menu
NEXT  BACK INDEX

25. 20. FORMAT PAINTER
The format painter provides a quick and easy way to copy and paste formatting. It’s not just limited to Excel – it’s available in
most MS Office apps. Select the cells to copy formatting, click the format painter, then click the cells you want to apply the
formatting to. All aspects of formatting are applied – borders, colors, alignment, number formats, fonts, styles, etc., which
can save quite a bit of time.
Description
All aspects of formatting will be copied. This may not necessarily be desired.
If using the format painter across filtered cells, the format painter often applies the formats even to unfiltered cells.
Gotchas
To apply the format painter to multiple cell ranges, double-click it with the copy range selected, then apply over and over.
The format painter can be used across different sheets, not just on the same sheet.
Tips
Format Picker – quickly apply common cell actions and set your favorite action as the default.
Paste Special Picker (option S) – apply paste special - formats only, based on the copied cells.
XLEV8 Add-in Macros
80 1/5 5 min Formatting Ctrl+Shift+V : paste formats
NEXT  BACK INDEX

26. 21. CELL STYLES
Cell styles are another newer, underused helpful formatting feature in Excel. They allow you to set several aspects of
formatting (number format, alignment, borders, fill color, and font type/size/color), and data protection. There are several
different native cell styles for headings, totals, accents, linked cells, input cells, calculation cells, error-check cells, hyperlinks,
and different types of formatted number cells.
Cell styles can be overwritten by modifying any of the formatting of ranges with cell styles applied.
Cell styles themselves cannot be locked, so save a backup or communicate to other users not to change them.
Gotchas
The biggest benefit of cell styles is being able to update them across your entire workbook in one step.
You can merge cell styles from one workbook to another workbook using the Merge Styles action in the Cell Styles menu.
Tips
Toggle Cell Style – cycle through five definable cell styles and back to no cell style.
Cell Style Picker – apply or configure a favorite cell style with a picker prompt menu.
XLEV8 Add-in Macros
30 3/5 60 min Formatting Alt-h-j-n : set new cell style
NEXT  BACK INDEX

27. 22. CONDITIONAL FORMATTING
Conditional formatting allows you to apply formats (fonts, colors, fill colors, and borders) based on certain conditions
(generally cell values like blanks or duplicates). For its immense value, conditional formatting is not used near enough. It’s
extremely flexible, efficient, and powerful. Call out the best or worst items. Find patterns. Highlight errors. Add polish to
your files. Leverage one of the built in conditions/styles or define your own. The uses are limited to your imagination!
Description
Conditional formatting overrides standard formatting – before you pull your hair out, check them both!
Rules are applied in priority order – when there are conflicts, the higher priority rule prevails.
Gotchas
Use conditional formatting wherever you can to quickly make values stick out (duplicates, differences, blanks, etc.).
Use the formula-driven rule type for the ultimate in flexibility when defining conditional formats.
Tips
Conditional Formatting Picker – apply (or clear) standard and custom formats with shortcuts and defaults.
Bulk Conditional Formats – apply specified conditional formats to multiple sheets/ranges in one bulk step.
XLEV8 Add-in Macros
100 3/5 60 min Formatting Alt-o-d : apply conditional formatting
NEXT  BACK INDEX

28. 23. DATA VALIDATION
Data validation allows you to guide users on what should be entered in cells, providing guardrails with a variety of options –
number ranges, dates, pre-defined lists, text lengths, and even custom formulas. Custom input messages (displayed when
the cell is selected) and custom error messages can be defined to provide instruction as to what types of values are expected
and valid. The list option is quite helpful, especially when combined with named ranges that expand as valid options grow.
Description
Without cell protection enabled, data validation can easily be overwritten or turned off.
The “Ignore Blank” data validation setting only really applies to circling invalid values – blanks can still be entered.
Gotchas
Use the input message option for data validation to provide guidance to users regarding what valid values are.
For dynamic data validation, link the validation rules to input cells containing the number ranges, lists, etc.
Tips
Data Validation Picker – apply (or clear) common data validation settings with shortcuts and defaults.
Search Data Validation List – easily search a data validation list using auto-complete as you type.
XLEV8 Add-in Macros
90 2/5 30 min Data analysis Alt-a-v-v : apply data validation
NEXT  BACK INDEX

29. 24. NAMED RANGES
Named ranges allow you to name a cell range or shape/picture/chart so that it is easier to reference instead of the range
address or default shape name. Named ranges can be used to quickly navigate with the Named Range box (to the left of the
formula bar). Cell ranges can also be dynamic, growing or shrinking with the contents of the range. They can be defined
with the Name Manager or the Named Range box. They work very well with other features like data validation and macros.
Description
Range names must begin with a letter, and can only contain letters, numbers, and underscores.
Range names can often become hidden or change in scope, causing performance issues or even crash.
Gotchas
Named ranges can be the result of formula functions (i.e. OFFSET and COUNTA), making them dynamic and/or
calculated.
Named ranges are helpful with macros and references in external files when the file structure might change.
Tips
Named Range Picker – apply (or clear) common named range actions with shortcuts and defaults.
Search Named Ranges – easily search for existing named ranges using auto-complete as you type.
XLEV8 Add-in Macros
85 2/5 15 min Structure Ctrl+Shift+F3 : create named range
NEXT  BACK INDEX

30. 25. MATH FUNCTIONS
Excel has about 80 functions in the math category, and many of them are among the mostly commonly-used functions in all
of Excel. Some of the key math functions for most Excel users to know are SUM, SUBTOTAL, AVERAGE, MIN, MAX, RANK,
SMALL, LARGE, SUMIFS, COUNTIFS, COUNTA, ABS, INT, RANDBETWEEN, and ROUND. Note that some of these functions are
found in the Other/Statistical category in the Formulas ribbon tab.
Number formats/text values could cause formulas to calculate incorrectly. Use a “XXX*1” helper column to troubleshoot.
Make sure to account for hidden rows/columns or structural changes when reviewing math formula results.
Gotchas
Consider using the SUBTOTAL function – it’s a super-versatile way to sum, average, etc. that can even ignore hidden cells.
SUMIFS is an extremely effective way to sum items using a variety of conditions and build a very dynamic report.
Tips
95 3/5 120 min Formulas and functionsAlt-m-g : insert math formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

31. 26. DATE AND TIME FUNCTIONS
Excel has 25+ functions in the date and time category, and when working with dates or times, many of them are extremely
useful. Excel stores dates as integers starting with January 1, 1900, increasing by one every day afterwards. Times are
fractions of a day. Some of the key date and time functions for most Excel users to know are EOMONTH, DATEVALUE,
DATEDIF, YEARFRAC, WEEKDAY, TODAY, DATE, YEAR, MONTH, and DAY.
Description
If you enter date arguments for dates that don’t exist, Excel will roll them over to the next year/month.
If dates don’t show up the way you’d like, check the number format local settings (Ctrl+1 / Number / Date category).
Gotchas
EOMONTH is extremely useful for building out monthly schedules, trend reports, etc.
The TEXT function is also very useful for displaying dates in a specific format in the middle of a formula.
Tips
95 3/5 120 min Formulas and functionsAlt-m-e : insert date formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

32. 27. LOGICAL FUNCTIONS
Excel has 19+ functions in the logic category, and they make Excel a borderline programming language. Logic functions allow
you to display certain values when conditions are met, which is extremely common in intermediate to advanced files. Some
of the key logic functions for most Excel users to know are IF, AND, OR, IFERROR, NOT, SWITCH, and LAMBDA. These are
often used together or with other functions to specify the conditions to evaluate.
Description
It’s easy to get lost in formulas when nesting several IF statements. Consider breaking them up for testing or reviewing.
It’s easy to have unintentional calculation errors (as opposed to syntax errors) in IF statements, so focus carefully!
Gotchas
If you’re looking to evaluate several different scenarios, consider the SWITCH function instead of nesting IF functions.
To check a certain number of conditions (i.e. 2/3 or 3/4), remember TRUE means 1 and FALSE means 0.
Tips
95 3/5 120 min Formulas and functionsAlt-m-l : insert logic formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

33. 28. LOOKUP AND REFERENCE FUNCTIONS
Excel has 37+ functions in the lookup and reference category, and many of them are frequently used. Lookup and reference
functions allow you work with arrays (lists) and cross-reference between data sets. Perhaps the most commonly-known
lookup function is VLOOKUP, which acts like a phone book returning a phone number when a name is searched. Other key
lookup functions are XLOOKUP, INDEX, MATCH, OFFSET, ROW, COLUMN, FILTER, UNIQUE, and INDIRECT.
Description
Lookup functions are notorious for returning error values. Check formula components, inputs, and even formatting.
Pay attention to the case of lookup data and the way returned data is sorted, as these can also cause issues.
Gotchas
The newer XLOOKUP function is an excellent enhancement that addresses many limitations of other lookup functions.
The polarizing INDIRECT function can be extremely useful when you need to build a dynamic file/sheet/range reference.
Tips
95 3/5 120 min Formulas and functionsAlt-m-o : insert lookup formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

34. 29. TEXT FUNCTIONS
Excel has 33+ functions in the text category, and many of them are very useful when cleaning up data, transforming data,
and working with data within the data. They allow you to combine, dissect, replace, and convert between text and
numbers. Some of the key text functions for most Excel users to know are CONCAT, FIND, REPLACE, LEN, REPT, MID, LEFT,
RIGHT, LOWER, UPPER, PROPER, REPT, TRIM, VALUE, and TEXT.
Description
FIND is case-sensitive whereas SEARCH is not. Most text category functions are case-sensitive.
SUBSTITUTE replaces one text string with another. REPLACE replaces text at a specific position with another.
Gotchas
The TEXT function is extremely useful for including specifically-formatted numbers/dates within a string of text.
The MID, FIND, and LEN functions work really well together to extract text within text in an unknown position.
Tips
95 3/5 120 min Formulas and functionsAlt-m-t : insert text formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

35. 30. OTHER FUNCTIONS
Excel has hundreds of other functions for financial, statistical, engineering, and informational purposes. These tend to be
used by specialists or in specific scenarios. Excel supports virtually any type of calculation that advanced calculators can
perform. Some of the key other functions for most Excel users to know are financial: FV, PV, RATE, PMT, NPER, NPV, and IRR;
statistical: COUNT, MEDIAN, MODE, and STDEV.P; informational: CELL, INFO, ISERROR, ISBLANK, and ISFORMULA.
Description
With financial, statistical, and engineering functions, make sure to handle errors that could arise with dividing by zero.
With financial time-value functions, make sure to specify beginning or end of period where applicable.
Gotchas
The CELL and INFO functions offer many helpful data points like the cell address, filename, formatting, and Excel version.
Excel is a great tool to use for loan amortization charts, especially when evaluating interest rate and payment scenarios.
Tips
95 3/5 120 min Formulas and functionsAlt-m-q : insert other formula
NEXT  BACK INDEX
Toggle Reference Types – easily toggles through the different reference types for all references in a cell’s formula.
Formula Picker – apply a variety of formula actions quickly with shortcuts, defaults, and custom favorites.
XLEV8 Add-in Macros

36. 31. FORMULA AUDITING
Formulas in Excel are abundant, flexible, and powerful, but they can also be frustrating, especially when you’re reviewing
someone else’s work and they do things differently. The more complex the formula and the more cell references it contains,
the trickier it can be to understand it and detect errors. Fortunately, Excel offers a variety of tools to trace the dependent
and precedent references, make sense of the flow, identify inconsistent formulas, and highlight errors.
Description
When showing formula errors, things can get messy quickly. Remove precedent, dependent, or all arrows to clean it up.
Just because there is no error doesn’t mean formulas are accurate or expected. Include sufficient checks to validate.
Gotchas
To see all the formulas within the worksheet (instead of the values), click Show Formulas in the Formula Auditing section.
Use the Watch Window to see how several cell values are affected by formulas all across your workbook.
Tips
Show Cell References – displays a searchable list of all active cell formula references and jump to any of them quickly.
XLEV8 Add-in Macros
50 3/5 30 min Formulas and functionsCtrl+` : toggle displaying formulas
NEXT  BACK INDEX

37. 32. EDIT AND BREAK LINKS
A great way to leverage formulas and cell referencing is linking different files together. This can help keep things clean and in
sync rather than having to maintain redundant information in several different locations. An example is maintaining an
employee listing in one file and linking to it with many different files. Links between files often break for a variety of reasons
(files move, the structure changes, etc.), so make sure to communicate to others when depending on certain files.
Description
Phantom external links can sometimes cause issues - examine named ranges, conditional formatting, and shapes.
Be careful when breaking links as it often undoes much work. Consider saving a backup linked file as a reference.
Gotchas
To update just a few links in your workbook, consider using the paste special – paste links option.
Editing links allows you update all links to a workbook in one bulk step.
Tips
Search Linked Files – displays a searchable list of files the active workbook is linked to.
List Linked Cells – lists all cells in the active workbook that are linked to external files.
XLEV8 Add-in Macros
60 2/5 30 min Formulas and functionsAlt-a-k : show edit links dialog box
NEXT  BACK INDEX

38. 33. INTEGRATIONS
Excel is the data import/export standard. Try finding a software application that doesn’t import or export data with Excel or
an Excel-compatible format. There are a variety of integration methods supported by Excel – JSON, XML, PDF, Image,
database, and API to name a few. Through VBA macros (Visual Basic for Applications), Excel can interact with and control
other apps like Word, Outlook, PowerPoint, File Explorer, and even web browsers!
Description
Data source changes, access changes, computer/source data internet connectivity can all cause issues retrieving data.
Integrations can be very touchy – if a file moves or contents change, an integration can fail or crash.
Gotchas
Set up data queries or connections that can be reused and refreshed over and over to save a lot of time.
Leverage the list structure and formulas to automate bulk actions across other apps (i.e. send emails or create folders).
Tips
Configure API Settings – configure import/export connections with all kinds of apps and design your own ribbon UI.
Run Browser Steps – configure website actions that Excel can automatically perform (clicks, text entry/retrieval/etc.).
XLEV8 Add-in Macros
85 4/5 120 min Efficiency Alt-a-f-w : get data from website
NEXT  BACK INDEX

39. 34. POWER QUERY
Power Query is an awesome tool for connecting to source data, transforming it (leaving the raw data intact), combining it
with other sources where needed, and loading it into a worksheet or data model. All those steps can be built for reuse and
easily refreshed when needed. Data can be imported from a variety of sources, including the web, a PDF file, a picture, and
many data file types. Transformations can be filtering, appending, transposing, and most Excel function types.
Description
Make sure columns/fields are assigned the right data type to be able to leverage type-specific actions/attributes.
Make sure to account for dynamic or variable data structures (extra columns, rows, record types, etc.).
Gotchas
If filtering your data, do it early in the process. Leverage the many type-specific and pre-defined filters available.
Leverage data profile tools to get insights into the data quality and distribution.
Tips
None yet
XLEV8 Add-in Macros
80 4/5 240 min Efficiency Alt-a-p-n-l : launch query editor
NEXT  BACK INDEX

40. 35. DATA TOOLS
Excel contains a variety of tools for working with your data – splitting it up, consolidating it, filling it in, cleaning it up,
removing duplicates, etc. Three features stick out among these as very common and useful: flash fill, removing duplicates,
and text-to-columns. Flash fill can automatically sense how you’re populating data and automate it. Removing duplicates
gives you a unique list of values. Text-to-columns lets you split up one column to several with a variety of methods.
Description
Unlike formula results, flash fill results are static and don’t make changes when the source data is updated.
Remove duplicates and text-to-columns will overwrite your data by default, so consider saving and back up accordingly.
Gotchas
Any custom lists that you’ve set up can be leveraged with flash fill and auto fill to quickly enter them.
Text-to-columns offers many options for splitting data (delimiters/fixed widths) and format the resulting columns.
Tips
Fill Picker – quickly apply several different fill options/directions and easily create your own custom fill list.
Paste Delimited Text – offers a quick and easy way to paste basic delimited text across columns or rows.
XLEV8 Add-in Macros
60 4/5 60 min Data analysis Ctrl+e : apply flash fill
NEXT  BACK INDEX

41. 36. GET DATA FROM PICTURE AND PDF
Data you need to work with in Excel is often in a screenshot, image, or PDF statement. Most people hand-key that data field
by field, line by line into Excel. There’s a much more efficient way – importing directly from a picture or PDF file! Both
options work surprisingly well, even when the image/PDF is slightly fuzzy. Even if it’s not perfect, it’s a better starting point
than having to type in everything!
Description
One of the biggest issues with importing from images is determining where the correct column breaks should be.
Directly importing data from one PDF file works well, but for many PDF files, a PDF data extraction tool may work better.
Gotchas
For images/screenshots, crop the image so that only the data you want to capture is included.
When importing from a PDF file, you can add manipulations and other steps that can be saved for reuse.
Tips
None yet
XLEV8 Add-in Macros
30 3/5 45 min Data analysis Alt-a-p-n-f-p : get data from PDF file
NEXT  BACK INDEX

42. 37. FREEZE PANES
Freeze panes allows you to freeze rows and/or columns above and to the left of your data set so that headings/labels are
always visible. This is especially helpful with worksheets containing hundreds of rows of data (freezing the top row(s) that
contain field headers), or with worksheets containing dozens of columns of data (freezing the left-most column(s) that
contain line item labels), or both.
Description
Freeze panes can be applied accidentally below/right of much of your data, making it difficult to scroll.
Freeze panes can sometimes cause display glitches, especially when adding cell comments/notes to frozen cells.
Gotchas
It’s a best practice to apply freeze panes to virtually all sheets, especially those that have tabular data.
Consider putting totals above your data set with freeze panes applied so you can always see them.
Tips
Misc Picker (option F) – freezes panes based on the selected cell, offering a quick shortcut.
Freeze Selected Sheets – freezes panes on all selected sheets based on the selected cell.
XLEV8 Add-in Macros
95 1/5 5 min Presentation Alt-w-f-f : apply freeze panes
NEXT  BACK INDEX

43. 38. HIDING/GROUPING COLUMNS/ROWS
Hiding or grouping/collapsing columns or rows is very helpful for ranges that don’t need to be displayed, but are used for
documentation or calculations and referencing. The best practice is to use grouping, which adds a toggle button, indicating
to users that ranges can easily be expanded or collapsed. Grouping is also helpful for setting up a hierarchy within reports
(i.e. financial statements) of roll-up line items, locations/regions, or dates. Grouping can be nested up to 8 levels.
Description
Hidden rows and columns can be tough to spot and can often contain information others shouldn’t see.
Lining up the right grouping levels is important for easy user navigation and analysis.
Gotchas
Leverage the different grouping levels to easily offer multiple views of your data in collapsed and expanded formats.
Use the SUBTOTAL function to add up just the visible cells that have been grouped.
Tips
Multi Sheet Format – apply hiding and grouping ranges across multiple sheets in one bulk step.
Misc Picker – apply hiding and grouping range actions quickly with shortcuts and defaults.
XLEV8 Add-in Macros
60 2/5 15 min Structure Ctrl+9(/0) : hide rows/columns
NEXT  BACK INDEX

44. 39. CLIPBOARD PANE
The clipboard pane was fairly popular for a couple of years, but was moved to the background to be less distracting and
make room for other features. It’s quite useful when you are copying and pasting a lot of different items between different
apps. It collects the last 24 items that you’ve copied so that you can quickly paste them again from the clipboard pane,
which is displayed on the left side when clicking the tiny icon in the Home ribbon – clipboard section.
Description
Items from the clipboard are pasted just as they are copied, along with any formatting.
The clipboard can sometimes get stuck or show an error. The clipboard pane Clear All button can often fix it.
Gotchas
You can leverage the clipboard collection without displaying it – enable the options in the clipboard pane options menu.
Starting with Windows 10, the clipboard pane can work across devices when signed in with the same Windows account.
Tips
Misc Picker (option C) – easily toggle the visibility of the clipboard pane.
XLEV8 Add-in Macros
40 1/5 10 min Efficiency Alt-h-f-o : toggle clipboard pane
NEXT  BACK INDEX

45. 40. COMMENTS AND NOTES
Notes (previously called comments) are a great way to add documentation and instructions to your workbook in a way that
does not clutter up your cell contents. They are denoted by a red triangle in the upper-right part of the cell. Hover over the
cell to show them. Threaded comments were introduced in 2019, offering user collaboration with the ability to reply,
mention other users, and mark as “resolved.” They are denoted by a purple caption shape in the upper-right part of the cell.
While notes can be formatted (text, images), resized, and repositioned, threaded comments cannot.
There is not an elegant, native way to show all notes in a worksheet (see the XLEV8 macro below for a great workaround).
Gotchas
Consider including the documentation around complex or revised formulas in a cell note.
Keep most notes and threaded comments hidden by default to keep your worksheet from looking cluttered.
Tips
Comment Picker – quickly perform common comment actions (insert, delete, resize/position, list all comments, etc.)
XLEV8 Add-in Macros
70 2/5 30 min Review Shift+F2 : insert/edit comment
NEXT  BACK INDEX

46. 41. SHAPES AND PICTURES
While not as common as with other apps like PowerPoint, sometimes including shapes or pictures in your Excel file is helpful
to add some polish, branding, or clarity for users. Excel has dozens of common shapes and hundreds of nice icons that can
be inserted, sized, and formatted to your liking. Pictures can be inserted, sized, positioned, and formatted from your own
collection, stock photos, or online libraries.
If you’re struggling with sizing or formatting shapes, check to see whether they are grouped.
Shapes and pictures can be distorted when changing column/row sizes unless formatted to not move/size with cells.
Gotchas
Shapes and pictures are referenced by names. Consider naming them logically and consistently to keep things organized.
When working with several shapes, use the selection pane (shortcut: Alt-h-f-d-p) to show a list of them all to select from.
Tips
Insert Shape Picker – quickly insert common shape types or one of five custom, formatted favorite shapes.
Format Shape Picker – quickly apply common shape formatting types with customizable favorite settings.
XLEV8 Add-in Macros
50 2/5 30 min Efficiency Alt-n-s-h : insert a shape
NEXT  BACK INDEX

47. 42. STATUS BAR
The status bar is extremely useful for quickly giving you information to guide your analysis and work. It’s commonly used to
show the sum, count, numerical count, average, min, and max for the values in the selected range of cells, but it can also
show other helpful information, such as the number of sheets in the file and whether caps lock, number lock, and scroll lock
are on or off.
Description
Statistical metrics are not displayed unless 2+ non-blank cells are selected.
Generally, you must click the Workbook Statistics button on the left side of the status bar to see the actual statistics.
Gotchas
When filtering, the left side of the status bar will show how many rows are displayed.
You can decide which metrics to show in the status bar – right-click it to check/uncheck the options.
Tips
None yet
XLEV8 Add-in Macros
60 1/5 15 min Efficiency (menu) : mimic right-click
NEXT  BACK INDEX

48. 43. NEW WINDOW
An extremely under-utilized feature in Excel (and other MS Office apps) is New Window. This feature will open a second
window of the active workbook so you can look at two separate sheets (or areas of the same sheet) at the same time. It’s
very useful for intricate workbooks where you need to see how cell references interact between different sheets. This is
most useful when you are working with multiple monitors and are showing different windows on each monitor.
Description
New Window does not mirror some settings (freeze panes, zoom level, and the horizontal scroll bar). See below for a fix!
Normally Excel saves just one window but sometimes multiple windows display when opening. Alert others if using it!
Gotchas
You can open as many windows as needed for an Excel file, but a max of two windows is recommended to limit
confusion.
Use the Synchronous Scrolling feature to scroll both windows at the same time while comparing data.
Tips
Misc Picker (Option W) – quickly opens/toggles a new window and optionally mimics window settings.
XLEV8 Add-in Macros
35 1/5 10 min Efficiency Alt-w-n : open new window
NEXT  BACK INDEX

49. 44. SIDE-BY-SIDE VIEW
The side-by-side view can be very helpful for reviewing related files or (using the New Window feature), viewing two
windows of the same file side-by-side. The separate windows can be arranged tiled (equally-sized squares), horizontally
(windows placed below one another), vertically (windows placed next to each other - default), or cascaded (windows overlap
one another).
Description
The default arrangement for viewing windows side-by-side is horizontal, but this may not be desired.
Sometimes Excel windows get stuck. Use the Reset Window Position (View tab > Window), or restart Excel after saving.
Gotchas
Side-by-side view works really well with the New Window feature for viewing two windows of the same file side-by-side.
Use the Synchronous Scrolling feature to scroll both windows at the same time while comparing data.
Tips
Misc Picker (Option W) – quickly opens/toggles a new window and optionally mimics window settings.
XLEV8 Add-in Macros
30 2/5 15 min Efficiency Alt-w-b : show side-by-side view
NEXT  BACK INDEX

50. 45. WINDOW VIEW
Within the Excel window, you can change many of the display options, including whether or not to display gridlines, headings
(column letters and row numbers), the ruler, and the formula bar. You can also change the zoom level to be able to better
see a specific part of your worksheet. While these items are helpful for navigation and editing, they may not be ideal when
presenting Excel on a large screen to a group. Note that window view settings are specific to a workbook or worksheet.
If the formula bar, gridlines, column letters, or row numbers are not displayed, check the settings on the View ribbon tab.
If your data is zoomed in or out too far to see, update the zoom level using the zoom settings on the View ribbon tab.
Gotchas
Toggle off the gridlines to better see how your worksheet will look when printed or troubleshoot borders.
To zoom to a specific section of your worksheet, consider using the Zoom to Selection option in the View ribbon tab.
Tips
Zoom Picker – quickly insert common shape types or one of five custom, formatted favorite shapes.
XLEV8 Add-in Macros
50 2/5 30 min Presentation Alt-w-q : set worksheet zoom level
NEXT  BACK INDEX

51. 46. PRINT SETTINGS
While printing to paper is not as common these days, it still happens, and it’s still fairly common to print to PDF to distribute
reports and data in a non-editable format. Excel offers a wide variety of print settings that are worth optimizing whether you
want to print to paper or PDF format: orientation, fit-to-pages (width/height), zoom level, header/footer, margins, repeating
rows at top/columns at left, and center horizontally/vertically.
Description
There are many print settings Excel cannot natively perform across multiple sheets in one step.
Columns can sometimes print differently than they display on the screen. Be sure to preview before finalizing printing.
Gotchas
To be very specific in how pages cut off, switch to Page Break Preview and drag the page breaks to where you want them.
If you don’t want to print the entire sheet (maybe just a summary), set the print area using print settings or page layout.
Tips
Print Picker – quickly apply common print settings and customize your five favorite print layouts.
Bulk Print Settings – apply print settings in one bulk step to multiple sheets (and optionally save settings to re-use).
XLEV8 Add-in Macros
60 3/5 60 min Presentation Alt-p-s-p : access print settings
NEXT  BACK INDEX

52. 47. UNDO AND REDO
Undo and redo are both extremely helpful – not just in Excel, but in most apps. It’s easy to accidentally mis-key something,
apply the wrong format, choose the wrong menu option, or paste to the wrong area. Use the undo action to quickly take
one or more steps back. Use the redo action to quickly reapply any steps that have been undone, or repeat the last action
taken.
Description
Remember that the effects of macros can not generally be undone. Save often, especially before intense macros!
After a file is closed, the undo action history is generally lost, so consider saving and closing at logical points.
Gotchas
Add the undo and redo commands to the Quick Access Toolbar to give you another option to run them.
Several recent commands can be undone with the small arrow next to the undo command.
Tips
Repeat Macro – repeat any of the last ten add-in macros run.
Repeat Command – repeat any of the last ten native Excel commands run via the add-in.
XLEV8 Add-in Macros
80 1/5 10 min Efficiency Ctrl+z/Ctrl+y : undo/redo last action
NEXT  BACK INDEX

53. 48. SHOW CHANGES
Show changes was introduced in 2022 and was a long sought after feature of Excel given that the same feature has existed
in other MS Office apps for some time. Because there are so many changes that can be made to workbooks and so many
changes tend to be made when working in Excel, it was a tricky feature to get just right, but it’s a welcome addition. A list of
content and structural changes is presented in descending chronological order in the show changes pane.
Not all changes are displayed – generally changes are limited to structural changes and content changes (not formatting).
Remember that the actions performed by macros are generally not tracked by changes (or the undo actions list).
Gotchas
To search through many changes, use the filter sheet or filter range option in the show changes pane.
Several changes made at once (i.e. clearing a multiple-cell range) are grouped together. Click see changes to see them.
Tips
None yet
XLEV8 Add-in Macros
75 2/5 45 min Review Alt-r-g : display the show changes pane
NEXT  BACK INDEX

54. 49. SHEET VIEWS
Sheet views were introduced in 2020 as a way to help users collaborate with data sets. A sheet view takes into account the
filtering and sorting such that multiple users can access the same sheet simultaneously but have different filter and sort
settings applied. Each user will see the data according to their own view settings. Sheet views are specific to each
worksheet within a workbook, so they cannot be accessed across separate files (or even sheets).
All views in a shared workbook are accessible by all users (none of the views are private).
Changes to the underlying data are saved regardless of the view used, and can affect the way views display data.
Gotchas
Even without collaborating, custom views can be very helpful for building reports or printing in a certain way.
If using sheet views, consider including a view of the default, unfiltered, original sort order you can easily get back to.
Tips
50 2/5 30 min Presentation Alt-w-v-n : set new sheet view
Filter Picker (option D) – apply saved default filter, sort, and column width views across separate files
XLEV8 Add-in Macros
NEXT  BACK INDEX

55. 50. DATA PROTECTION
Data protection is one of the most misunderstood features in Excel, and while it’s not used that often, it’s still quite useful.
There are several methods of protecting or securing your workbook – locking and unlocking specific cell ranges, protecting
individual worksheets, protecting entire workbooks, and securing opening files with passwords (for both edit and view-only
usage). Worksheet protection can be applied very granularly (everything or just structure, formatting, content, etc.).
Description
Even with complex passwords, data protection and access passwords are relatively easily to circumvent.
All cells are “locked” by default, but this does nothing without applying data protection to the worksheet or workbook.
Gotchas
A great use of data protection is to only allow changing input cells after formulas and structure have been reviewed.
For key shared files, consider applying an “open as read only” warning so users are less likely to make accidental changes.
Tips
None yet
XLEV8 Add-in Macros
30 3/5 60 min Data analysis Alt-r-p-s : protect worksheet
 BACK INDEX

56. Written by Derek Henry, CPA, CFE
www.excelevate.co
© 2023 Excelevate Consulting, LLC, all rights reserved.
Excel is a registered trademark of Microsoft Corporation.