Category Archives: Excel

5 Amazing Must Have Excel Tips

Have you stared at the data in your worksheet and wondered how you were going to get the same information in multiple cells, or how to automatically insert a cell with a full name from two cells with the first and last name, or how to best graphically display those sales figures? Well, hopefully, these 5 amazing must have Excel tips will give you some answers!

Enter same content in multiple cells simultaneously

Need the same labels or values in several cells?

  • Select the first cell
  • CTRL click on the other desired cells
  • Type Desired content
  • Press CTRL ENTER

Presto! Same content in all the cells.

Same content in multiple worksheets

Need different content in several cells to display in multiple worksheets in the same cell references? Group worksheets and just type it once!

Group desired worksheets by selecting worksheet name tabs:

  • If worksheets are not adjacent, click first one, and then CTRL click on each worksheet you want to have identical content.
  • If adjacent, click on first one, and SHFT click on last one (or right click and choose Select All).
  • Now type in as many cells as you want on the active worksheet (can be different data in each cell). Same contents are now in corresponding cells in all selected worksheets.
  • To Ungroup, click on a worksheet tab outside of the group, or right click on a worksheet tab and choose Ungroup Sheets.

TIP: This is a terrific shortcut for adding the same headers and footers or printer or page setup settings to multiple worksheets simultaneously. Also works for creating duplicate content for days, months, quarters or any repetitive unchanging data; basically creating an in-workbook template!

Number Worksheet Rows

Can’t figure out number of rows because your data starts several rows from the top or some other area of the worksheet?

Excel allows you to number the rows and will auto adjust when you add or delete them if you use the =ROW() function. The key is to deduct the number of rows above the one you are starting in so the numbering will always be correct.

Example: We’ll use Row 12:

  • Insert a column or click in the column where you want the numbering
  • Type: =ROW()-11 in row 12 of that column. The number 1 is displayed
  • Fill down desired number of rows.

You now have the actual number of rows you are working in!

Speaking of numbering, getting Excel to automatically create a series of sequential numbers, requires some gentle nudging…

Sequential Numbers

I you want to populate a column with sequential numbers, just enter the numbers in cells, such as



Select both cells and drag the Fill Handle for as many numbers as you wish. Excel will automatically iterate the count. Start with any number.

It will also work with jump numbering, such as this:



Select both numbers and drag down for the desired number. The next numbers would be 9 and 13.

Note: This also works with dates and text.

TIP: Here’s a trick for fast sequential numbering; just type your first number, hold down the CTRL key and drag the Fill Handle. Excel will recognize the pattern. (For non-sequential numbers, you still need two examples).

Like then so far? Let’s look at a couple more must have Excel tips!

Flash Fill

Flash Fill is a time-saving feature that reads patterns in adjacent columns and automatically fills the remaining cells in a column based on those patterns. It is useful when you need to concatenate (join) cells, or separate information in cells without wanting to write a cumbersome formula. Kind of like AutoFill on steroids! This amazing tool will make short work of many repetitive tasks, all without formulas (or the Text to Columns feature)! Here’s some things it can do:

Splitting Input Data

You received a huge spreadsheet where someone has put first and last name in one column, and you need them separated for sorting and filtering. (This could be any data, i.e., department name and phone extension, or salesperson and monthly sales figure. Doesn’t matter as long as the data has a separator, such as a space). Flash Fill to the rescue…


Full names are in Column A.


  • Insert: two columns to right and enter appropriate column header labels.
  • Type: Fred, in appropriate cell (in example, Cell B2)
  • Press: ENTER
  • Type: Mary (note that Excel has figured out what you are doing and is displaying the remainder of first names, and is awaiting your approval)
  • Press: ENTER, and presto, all the rest of the first names are added!

Flash Fill Splitting Input Data

Repeat the above steps in the cells for Last name (In example, Cell C2)

List is now complete:

Flash Fill First and Last Name cells

TIP: You can have flash fill complete only the last name, i.e., just insert one column, and instead of typing “Fred”, you would type “Frump”, ENTER, then “Lamb”, ENTER, and you would be done.

Note: Give Excel one or two examples of what you are trying to accomplish so flash fill can see a pattern. It will work with text, dates, numbers and time. Be aware that if you change source data, cells containing flash fill data will not update as there are no formulas involved.

Combining Data from Different Columns

The reverse is also true. You have a worksheet with first and last names (or any data) in two columns and you want them combined into one. This used to require the CONCATENATE function but now Flash fill can do it automatically. Drop kick that CONCATENATE function off your spreadsheet. Tough enough to spell it, let alone use it!

Here’s the data in two columns and how to combine into one:

Flash Fill combined cells


  • Insert: a blank column to the right of split data
  • Type first full name in the new column (C2)
  • Press ENTER
  • Type second full name, and before you are finished, Excel sees you want the remainder filled in
  • Press ENTER to complete the list

Extend the Combined Data

You just got word that you need to add the email addresses for all the people in your company that you just did your flash fill magic on (or any such list). To make it worse, the addresses are last name first. Flash fill is not intimidated and has your back:

  • Insert your new, blank column to the right of the Full Name column
  • Type the email address in the first cell
  • Press ENTER
  • Start typing the second email address, and the dependable flash fill displays
  • Press ENTER to complete the email list for the remaining cells.

Flash Fill auto email addresses

You have saved so much time, you go to coffee!

Keyboard shortcut lovers…Here’s an even faster way to activate flash fill. Type the entry in the first cell. Press CTRL ENTER, and then press CTRL E and the column is filled in a flash!

TIP: If you accidentally lose the Flash Fill display, click the Data tab, and in the Data
Tools group, click the Flash Fill icon (ensure you are in the second entry in your column). If the ribbon and Flash Fill are greyed out, click outside your data range and then click in the second cell again and retype.

There is so much more that Flash Fill can do, so play with it and see if it will display that very thing you want.

Note: If Flash Fill is not working and the above tip doesn’t activate it, it may be corrupted or missing system files. There is an easy fix and download thanks to Microsoft Gold Certified Partner, Scott Chan. Just follow the information and link on his website: Scott Chan PC Support

The Quick Analysis Tool

You’ll find this a really helpful feature whether you are an experienced user or fairly new to Excel. Just select the data to analyze, and the Quick Analysis icon appears in the bottom-right corner of the selected data.

Click that icon, and a dialog appears showing a range of tools for analyzing the data, such as Formatting, Charts, Totals, Tables and Sparklines. Click any option, and a series of selectable choices appear; preview those choices by mousing over them. Next, click the option you want to apply it to your data. This feature speeds up the process of formatting, charting and writing formulas.


  • Select: the cells to analyze. When the mouse is released, you see the Quick Analysis Tool icon at bottom right of the selection:

Data selected with Quick Analysis tool displayed

  • Point: at the icon to display the tool tip:

Quick Analysis icon

  • Click: the icon to display the options:

Quick Analysis tool options displayed

  • Roll your mouse over each of the options and when you see the one you want, just click!
    • Formatting: Adds Conditional Formatting to your selection based on Excel’s default rules
    • Charts: Displays the main types of charts that will work with your data
    • Totals: Sum, Average, Count…The equivalent of AutoSum feature. Running totals and percentages also available
    • Tables: Automatically converts your data to a table with the filters in every column
    • Sparklines: Displays miniature charts in the cells to the right of each row of data (3 types available)

Example of choosing Formatting: Selecting Greater Than under Formatting, Excel will automatically display the rule it used to color every cell with a number over 6065 pink. You can then make adjustments right on the screen. You can change the base number here and also if pink isn’t your color, change it here!

Conditonal Formatting displayed from Quick Analysis tool

TIP: You can apply any one of these and then edit if you want to change the rule or behavior. For example, to see more options for the above, Greater Than Quick Analysis tool, (or any of the other Conditional Formatting options):

  • Click the Home tab on the Ribbon, Styles Group, and click the drop down arrow on Conditional Formatting icon, (Note that many more conditional formatting choices are available on this drop down list).
  • Select Manage Rules (bottom of list).
  • Select the rule, and click Edit Rule to display options. (Can also Delete Rule here)
  • Click OK to confirm any changes.

Note: If you want this tool to put in the formulas for you, such as totals, averages, counts, etc., don’t enter them yourself until you see if the automatic calculations under Totals: work for you!

See more awesome Excel tips and tricks here: or use Search for a specific topic.

Which feature is most helpful to you?

Inspect and Remove Sensitive Document Properties with Document Inspector

It may come as a shock to some just how much information Microsoft collects in Document Properties during the creation of a file. This blog reveals how to inspect and remove sensitive document properties with Document Inspector. We have enough to handle just getting the document to be correct and look the way we want to present it without worrying about broadcasting sensitive data!

Office collects personal data

There are positive uses for this information and even for creating our own custom Document Properties, but we’ll cover that in a future blog as this is about protecting your information in Word, Excel and PowerPoint.

If the document is not leaving your computer or office, it may not be an issue to have unexpected details revealed about the creation path and timeframe for editing, how many revisions and more. On the other hand, if this is confidential or going to another department, or worse yet, to a client or outside organization, there could be a problem Houston!

NOTE: Document Inspector tends to be an all or nothing thing. Might want to create and save a copy of the document before you inspect, remove and send, because it may remove elements that you want to keep in your original.

What Data is Collected

As you work, here is what starts to accumulate about your document and you (or the user who is working on it).

Click File tab to display the Info screen (Backstage) with the Properties of the current document (left screenshot):

Word Document Properties

Much of the data is what you would expect to see if you were looking at the file in a directory, but note there are fields such as Total Editing Time, Author, Last Modified, Last Printed (and by whom).

If you click the link for Show All Properties at the bottom of the screen, you see more information is revealed such as Company and Manager (shown on the screenshot on the right). There are also several fields where you, the user, can enter details to identify the file for searching and clarity.

Let’s go one step further…At the top of the Properties column (in either screen), click the dropdown arrow and then click Advanced Properties button:

The Advanced Properties dialog box will display with five tabs that collect different data:

  1. The General tab contains the information you would see with the Details view in a directory.
  2. The Summary tab is where you can choose to add your own information to identify and describe the document.
  3. Statistics tab contains some file details but adds personal data about the construction of the file.
  4. The Contents tab pulls document properties from fields. For example, if you added a Title in the Title tab, it will appear here.
  5. The Custom tab is where you can add properties from the list such as Department or Editor or create your own.

As mentioned, you may want to utilize these properties for various reasons but, for now, we’ll just concentrate on what Word is collecting and tracking, and how to get rid of the information, if needed.

Note: Previously you could display the Document Panel from Advanced Properties directly at the top of your document and fill in the property tags there. It was removed from Office 2016.

Review with Document Inspector

Let’s look at all the document and personal information being collected:

  • Click the File tab and ensure Info is selected.

Inspect Document screen

  • Click Inspect Document under the Check for Issues dropdown arrow.
  • The Document Inspector displays where you can choose what content to check for.
  • Leave them all checked and click the Inspect button at the bottom of the dialog box.

Document Inspector dialog box pre-run

The same list displays again with the requested data flagged with a red exclamation mark and a list of the information found.

Document Inspector after running

Remove Hidden Data

If you want that data deleted, click the Remove All button. Click Reinspect to ensure it is gone or remove other information.

You can now send that file without fear that it is revealing your inner most document secrets, but you might want to take it one step further if the document has ever been shared, or you have cropped images! (See below).

Document Properties in Excel and PowerPoint

Both these programs use the same method for collecting data about your file but because of their diverse purposes, track some different information. You inspect and remove the same way with the Document Inspector.

Like Word, Excel and PowerPoint collect data on:
  • Comments, and Annotations
  • Document Properties and Personal Information.
  • Invisible Content
  • Custom XML Data
Excel adds:
  • Headers and Footers
  • Hidden Rows and Columns
  • Hidden Worksheets
PowerPoint adds:
  • Off-slide Content
  • Presentation Notes
 Word adds:
  • Revisions and Versions,
  • Metadata, Microsoft SharePoint properties, custom properties, and other content information.
  • Headers, Footers, and Watermarks
  • Hidden Text
  • Task Pane add-ins

How to Inspect and Remove

The same for all programs. Go to File | Info |Check for Issues | Inspect Document.  Note the list of things that will be inspected, leave them all selected and click the Inspect button.

Check Before Sending

There are some things not covered by the Document Inspector that could cause embarrassing or legal issues if the original information remained intact. Cropped images may display as you edited in the document, but the complete original image remains unless deleted. Same is true of Tracked Changes that have been edited if someone turns on All Marks.

Delete Cropped Areas of Images

  • Click on an image
  • In the Picture Tools | Format tab | Adjust group, click Compress Pictures

Compress pictures and delete cropped areas

  • Ensure there is NO checkmark in Apply only to this picture.
  • Ensure there IS a checkmark in Delete cropped areas of pictures.
  • Click OK.

Remove Tracked Changes

Accept or reject tracked changes to remove them from your document:

  • To look at each revision one at a time, on the Review tab, click Next in the Changes group, and then Accept or Reject.

Use Ribbon to remove tracked changes

Word keeps or removes the change and then moves to the next tracked change.

  • To accept all the changes at the same time, click the arrow below Accept, and then click Accept All Changes.
  • To reject all the changes at the same time, click the arrow below Reject, and then click Reject All Changes.

IMPORTANT:  Choosing the No Markup view helps you see what the final document will look like, but it only hides tracked changes temporarily. The changes are not deleted, and they’ll appear again the next time someone opens the document. To delete the tracked changes permanently, you’ll need to accept or reject them.

Whew! Now your clean and lean document can be sent without all that hidden data. If you want more information on security for your Office files, see the related blogs…

Have you had any surprise experiences with sharing sensitive information? Let me know in the Comments.


Using Range Names in Formulas

Have Excel Automatically Create Range Names

A quick way to create range names is to base them on heading cell text (worksheet labels). In the example shown below, the cells representing quarterly sales for all regions will be named based on the labels in columns B through E.

NOTE:  If the labels contains spaces, those are replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

Excel worksheet with column and row labels

Name Cells or Ranges Based on Worksheet Labels:

  • Select the cells that you want to name, including the labels. These can be above, below, left or right of the cells to be named. Here, we are having Excel name the four quarter labels all at once by selecting B4:E8

Selected cells with column labels for naming ranges

  • Click the Formulas tab on the Ribbon, then Create from Selection in the Defined
    Names group.

Create names from selection

  • Excel will automatically place a check mark for the location of the labels; assumes you want to use the first cell as the name (in this case Top Row of the selection).
  • Click OK to add the range names to the Name box and the Name Manager.

Name box with range names

  • Click the dropdown arrow on the Name box to see your new range names. Just click one to highlight the included cells (Quarter1 would highlight B5:B8 as B4 is the label and not included in the range).

    : If there are spaces in the labels, they are replaced with an underscore. Quarter 1 would become Quarter_1.

Auto apply range names for the row headers by selecting the text in Column A through the numbers in Row 8 – A5:E8 (don’t include totals):

Cells selected with row labels

Repeat Steps 2, 3 and 4. Note that the check box is now Left row. Click OK.

Display range names from the Name Box:

Name box with alpha range names list

Notice that Excel alphabetizes the list no matter what the order of creation.

Before we put those names into action, we probably will want to know totals for all the eastern regions and all the western ranges at some point, so we’ll create range names for them. Also, we’ll abbreviate the individual regional names so they are much easier to use in our formulas:

Regional Annual Quarterly Sales sheet

Since we are naming the range, we only select the numerical cells:

  • Select B5:E6 (all quarterly sales for the two eastern regions)
  • Click in the Name box and type East
  • Press ENTER

Do the same for both the western regions:

  • Select B7:E8
  • Click in Name box and type West
  • Press ENTER

East and West names are added to the Name box list and the Name Manager.

Now, add a section below your existing data to capture total and average sales for the two regions:

Spreadsheet with Total and Average Sales area

We could call it done and begin using the created names in formulas but the regional names are too long, so for efficiency, let’s change them to just two letters.

Change a Named Range

After you create a named range, you might need to adjust the referred to cell references or, in our case, abbreviate the name to make it easier to use in a formula. Here’s the steps:

  • Click the Formulas tab, and in the Defined Names group, Click Name Manager icon
  • Click on the name that you want to change In the list (in this case Northeast)

Name Manager dialog box

  • Click the Edit… button or double click the name to display Edit dialog box
  • The name field shows the name highlighted
  • Type NE in the box to replace it
  • Click OK

Edit Name dialog box

  1. You can now choose the other three long regional names and replace with NW, SE and SW using Steps 3 through 6
  2. When completed, click the Close button in the Name Manager

Now we’re ready to rock and roll using them in our formulas!

Use Excel Names in Formulas

The real fun begins! We saw in earlier posts how to use range names for navigation or selection – very handy but here’s the real power. We want to find the totals for eastern and western regions for the four quarters. Because we made separate range names for the east and west regions, we can create formulas for the northern or southern regions and/or east and west like this:

  • In B13, type: =sum(ne,se) ENTER   (Range names are not case sensitive so can be typed lower case and Excel will convert)

Formula for Total Sales East Coast

Note: Another advantage of range names is that they are added to the auto display list along with function formula names and you don’t have to remember any cell references!

Next, we’ll simplify even more using the east/west names:

  • In B14, type: =sum(east) ENTER

Presto! There’s the total for the Northeast and Southeast regions.

For the average section, formulas are the same, changing the function:

  • In B17, type: =average(east) ENTER (or you could type: =average(ne,se) ENTER
  • In B18, rinse and repeat with (west) for final results!

Total and Average Sales results cells

TIP: If you need to change the cell references for a range name, open the Name Manager; select the name, and edit the contents of the Refers To box, or highlight the new range on the worksheet with the mouse, and Excel will edit for you. Click the check mark to save the change and close the Name Manager. No need to retype anything.

Your names can be created from references on one worksheet and the formulas used on another because their scope is available across the workbook. I’m pretty smitten with named ranges myself. What do you think? Do you see lots of uses for using this feature? Let me know in the Comments. Thanks!

For more information on Range Names, see: and


Use Excel Name Box for Navigation and Selection

In a previous post we covered navigating in Excel using the Name Box by typing a cell reference or a name for a cell or range of cells in the box or using the Go To dialog box (see link below).

compass for navigating your worksheet

When you name cells, they are called Range Names and become much more powerful, or at least more convenient, compared to using cell references.

Name Box for Navigation

The box reflects the current, active cell but does so much more. Although it looks separate, it is really part of the Formula Bar, so if you hide the Formula Bar, it disappears also.

The Excel Name Box

A quick recap on accessing the Name Box. There is no keyboard shortcut for landing there but you can press F5 function key to display the Go To… Dialog box and type in a cell reference in the Reference box; press ENTER or click OK, to go directly to that cell.

The Go To Dialog Box

Name Box for Selection of  Cell Ranges

Selecting huge ranges of data can be frustrating but if you know the cell references for the desired selection, the Name Box is the way to go. Just type in the range (or as close to it as you remember), i.e., a150:r8765, press ENTER, and that entire range is highlighted.

Tip: If you forgot column(s) or row(s) or included too many, just use SHFT and arrow keys to add or remove them. (Beats re-selecting two or three times).

Create Range Names in Name Box

The real power of the Name Box is to use it to create a name or ID for an often-used cell or range of cells, such as Commission or Regions, etc. You can create names that refer to cells, formulas, or a specific value.

The names can then be used for quick navigation or in a formula instead of cell references. You can create as many named ranges as desired and they are accessible from any worksheet in that workbook.

Steps to create range names:

  • Select the cell(s) that you want to name
  • Click in the Name box
  • Type a name for the cells (descriptive but short). There are some rules for range names:
    • Must start with a letter or an underscore
    • No spaces (can use an underscore to represent a space) *
    • Name may contain letters and numbers and periods but nothing that could be mistaken as a cell reference (examples: C, 2, R7C4)
    • Not case sensitive
    • Is ABSOLUTE cell referencing by default
    • Press ENTER after naming you range

 * Examples of naming a range could be: TotalSales or Total_Sales

Range Name example

Note: If there is a formula in that cell, it displays in the Formula Bar and the applied name in the Name Box.

I can now be anywhere in my workbook and return to that range with a click on the dropdown arrow in the Name box:

Name box with cell name

Now I don’t need to know the cell reference or even where it is located and can use that name in a formula, either for part of the formula or with other range names. For instance, if I had a commission rate in F8 and had named that cell, my formula would be: =TotalSales*Commission from any cell in my workbook. How sweet is that!

Name Box Limitations

There are several ways to create named ranges but the Name Box is the quickest. Be aware though that you can only create range names here, you cannot edit or delete them. That must be done from the Formulas tab on the Ribbon. You can also have Excel create range names for you using existing row and column labels to name them (covered later).

See original post for ways to navigate your worksheet:

See the blog on Using Range Names in Formulas for more ways to use this great feature!

Are you liking this handy Name Box? Let me know in the Comments below!

Use Excel Sparklines Instead of Charts to Display Data Trends

What are Sparklines?

Sparklines were introduced in Microsoft Excel 2010 and add a quick way to display results without having to insert an entire chart object. A sparkline displays a visual representation of data as a tiny chart inside a single cell, and can be used to show trends in a series of values, such as sales for a company, products, sales representatives, time frames, economic cycles, or pretty much anything that has discernible increases or decreases.

Is a Sparkline a Chart Object?

The tiny sparkline chart actually resides in the background of a cell and displays a separate result in each cell for the range selected, unlike the chart object that displays all results in a single chart, such as a column, bar, pie, or a myriad of other chart types.

There are three different sparkline chart types, found on the Insert tab:

Sparklines Group

Create a Sparkline Chart

Because sparklines live in the background of a cell, you can insert them anywhere on your worksheet. However, people wouldn’t have a clue what they were supposed to represent as the Formula Bar is blank for any cell that contains a sparkline, unless you enter something else in the cell. Ergo…Best to put them next to your data.

Sparklines are automatically grouped by default but you can ungroup them to treat the cells separately. When you insert the chart, you can click in the first cell where you want the chart, and then select the rest of the range when inside the dialog box, or easier method, select the range first, and it will be auto displayed in the box:

Range to insert Sparklines

Click Insert tab to display the dialog box. If you have selected all the cell references for results, the Location Range is filled in and the Data Range: box is waiting for you to select the value cells:

Create Sparklines dialog bocx

Select the full range with the mouse of the values you are charting (in this case, B4:E8):

Data range for Sparklines

Click OK to insert the mini-charts.

Line Sparklines inserted next to data

Note: If you only selected the one cell, F4, when you inserted the sparkline, you can use the Fill Handle to populate the rest of desired cells. (You must drag the handle down though as the double click shortcut will not work here).

The Design Tab for Sparkline Tools

You don’t have all the fancy trappings of actual Excel chart objects but, hey, these are pretty spiffy and sometimes can even get the picture across in a clearer manner. The Sparkline Tools tab gets added to the end of the Ribbon and gives you many options for editing and formatting those little gems through the Design tab.

Sparklines Tools Design tab on Ribbon

You probably noticed that no markers displayed on the line charts but you can easily add them. (They are available only on this chart type). In the Show group, just click in the Markers box, and there you go – a marker for each change in value. Now you can use the Style group to choose a quick color change or individually change the Sparkline Color or Marker Color. (See below). Individual markers are also available for any chart type to represent High, Low, Negative, etc., in the Show group.

You can apply a color scheme to your sparklines by choosing a built-in format from the Style gallery or change your chart type from the Design tab (available when you select a cell that contains a sparkline). You can use the Sparkline Color or Marker Color commands to choose a color for the high, low, first, and last values (such as green for high, and orange for low).

Whatever selections you make are applied to all the sparkline cells as they are grouped by default. If you want to personalize a particular cell, or all cells individually, they can be ungrouped, and you can also type a comment directly in the cell without deleting the chart.

Ungroup Sparklines

Ungroup all the sparklines by selecting  that range, and choosing Ungroup from the Group group. (I’m not stuttering, honest)! If you just want to personalize one cell, select it, and choose Ungroup.

Sparkline cell with text entry

Using Sparklines for Stock Performance

This image shows a column sparkline in cell F2 and a line sparkline in F3. Both of these sparklines get their data from cells A2 through E2 and display a chart inside a cell that shows the performance of a stock. The charts show the values by quarter, highlight the high value (3/31) and the low value (12/31), show all the data points, and show the downward trend for the year. The high value marker is green, and the low value marker is orange. All other markers are shown in black.

Stock Performance Sparklines

Cell F6 shows the 5-year performance for the same stock, but displays a Win/Loss chart that shows only whether the year had a gain or a loss. This sparkline uses values from cells A6 through E6.

Sparkline Benefits

Sparklines can be inserted next to the data and take up such little space. They also easily point out a pattern. Any edits to your data automatically update the chart so the changes to trends are instantly represented. They can be inserted for rows or columns of data, and type changed with a mouse click.

*Stock Performance image from Microsoft

Find Sparklines fascinating? What would you use them for? Leave a comment below!

If you like different graphical ways of displaying your data, check out my blogs on using the Camera tool in Excel:

Part 1…

Part 2…

Copy and Paste Filtered Subtotals or Visible Cells Only in Excel

Copy and Paste Visible Cells Only (filtered data)

You have used one of several methods to hide some rows for filtered data, or created a table which auto applies filter icons for each column. Now you want to copy and paste just the visible data but discovered to your horror when you pasted to another location, it included the hidden rows!

Excel, Paste only Filtered Data

By default, Excel copies hidden or filtered cells in addition to visible cells. If you want only visible rows, here’s the steps:

  • Select the cell range that you want to copy.
  • Click Home tab, Find & Select in the Editing group and choose Go To Special
  • Click Special… button in the dialog box.
  • Click Visible cells only radio button and click OK.

Excel, Copy and Paste Filtered Cells Only

  • Click Copy in Clipboard group on Home tab (or press CTRL+C).
  • Click the upper-left cell of the desired paste area and click Paste (or press CTRL+V).

You have now achieved Nirvana!

Tip: You can also use the F5 Function key at Step 2 to bring up the Go To… dialog box and click the Special… button to get the same results.

Add the Icon for Select Visible Cells to the Quick Access Toolbar

Make this great solution even easier and faster by utilizing the Quick Access Toolbar (QAT):

  • Right click anywhere in the Ribbon and choose Customize Quick Access Toolbar… OR click the QAT dropdown arrow, and choose More Commands
  • From the Choose Commands From dropdown, choose All Commands.
  • Scroll down and click Select Visible Cells.
  • Click Add and then click OK to add to end of the QAT.
  • If desired, use the arrow boxes to change the icon’s position on the toolbar.

Now all you do is select the range, click the Select Visible Cells icon on the QAT, Copy and Paste – One and done!

Copy and Paste Only Subtotaled Rows

You’ve used the SUBTOTAL function to sum only filtered data and now want to copy and paste to another location. You assume the paste will include the visible subtotaled rows only – Surprise – not! You still need to use the Go To dialog box to accomplish this but if this is something you do often, apply shortcuts:

  • Select the range you want to copy. (Excel is actually selecting the hidden rows as well but this will get taken care of in the next steps).
  • Press F5 function key to display Go To dialog box.

Excel, Copy and Paste only Subtotaled, filtered Rows with Special...

  • Click the Special… button at the bottom of the dialog box.
  • Click Visible Cells Only to select only the visible cells in the selected range.

  • Click OK (or just hit ENTER key as OK is already selected).
  • Press CTRL C to copy the selected visible cells to the Clipboard.
  • Select a destination cell (can be on the same sheet, a different sheet, or on a new workbook).
  • Paste the range by pressing CTRL V. Excel copies only the subtotaled rows.

Now you can copy and paste only those cells or ranges YOU want.

Hope these tips have removed a little head-scratching from one of those features that we want to use on a regular basis but doesn’t always behave the way we expect!

Display Results of Formula Inside Cell During Creation

Would you like to see the values displayed when you are entering a formula but a little fuzzy on how to make that happen?

Get clarity displaying formula values in cell

There are times when you may want to examine the results of a complicated formula from others or as you are creating one inside the cell. This can save time and frustration and avoid the dreaded error message after you hit ENTER or TAB.

If you want to look at the results of a particular part of a formula as you type, or after you or someone else has entered it, use a couple of function keys to complete the process.

Display Formula Values During Formula Creation

Here is a simple example of how handy this can be where we are adding sales of only two of the products, adding fees and dividing by cost, with the formula being entered in H9:


Example displaying formula values in the cell

If I want to know the value of G9/F9 before completing the formula, I can do this directly in the cell.

  1. In the results cell (in this case, H9), type the formula.
  2. Select the part of the formula whose value you want to see. Here, it would be G9/F9.
  3. Press F9. Excel replaces that part of the formula with its result.

Selected portion of formula displays values

If this is the correct result, press ENTER or TAB, and Excel completes the formula and moves to requested cell. You could also press ESC to return to your formula and stay in the cell. Be careful here. If you are creating, not editing a previous entry, ESC will revert to whatever the cell contents were prior to your entry (such as a blank cell).

Display Formula Values Editing Existing Formula

If the formula already exists and you want to display the value, the steps are the same except you need to be in Edit mode in the cell:

  1. Click in the cell and press F2 to edit the formula in the cell. *
  2. Select the part of the formula whose value you want to see (G9/F9).
  3. Press F9. Excel replaces that part of the formula with its result.

*Note: Since the content already exists, you could also double click in the cell to edit. Only difference is that F2 places your insert marker at the end of the contents, and wherever you double click, the insert displays at that point.

Use the Ribbon to Evaluate Formula

There is another way to evaluate a formula by each cell reference – Select the Formulas tab, Formula Auditing group and click on Evaluate formula icon to bring up the Evaluate dialog box.

Example of Evaluate Formula dialog box

Click Evaluate button to see formula results as you move through each cell reference. This is a great way to walk through a formula you are not too familiar with and/or someone else created and has you scratching your head.

Now you can know what is going on in your worksheet before you press Print or Send!

Let me know if you have used these features or how they can help you in the future…

The Excel Camera Tool, Part 2

Create a Dashboard Using Camera Tool

We saw in Part 1 how to add the Camera tool to the Quick Access toolbar and how to use it for capturing linked pictures that automatically update when the source data is changed.

Another great use for the Camera tool is to create dashboards. The pasted objects can be sized proportionately and positioned anywhere on a worksheet. Here’s an example of sales figures and a chart:

Dashboard Example

This can contain as many objects as you choose. Note that the location of the original data is displayed in the Formula Bar.

Arranging as images on a worksheet in a dashboard-like mode is also a great fix for printing all your related information on one page. If you had selected all your original data separately, even from the same worksheet, the Add to Print command would have to be used which would automatically print each addition on a separate page. This way, you can add images, move, size and print at will.

The Camera tool captures everything as an image, including values, color formatting, shapes, even the gridlines. Because it is an image, the Picture Tools | Format tab is displayed on the ribbon so anything you can do to a picture, you can do here, including sizing and rotating. Use the right click menu to quickly perform commands such as Crop:

Moving Original Data

The Formula bar displays the path of the original data and the cell references are absolute by default. This is terrific because if the original data gets moved to another area, your pasted pictures reflect that, and continue to display any updates. Should be automatic but be sure that the workbook name (if different) and worksheet name display as well as cell references. If your workbook and worksheet are named, could look like this:

The Paste Special Option

The Paste Special Linked Picture option is available in Excel versions 2007 and above. If you prefer Paste Special, you can access it from the drop down arrow on the Paste icon in the Clipboard group or by right-clicking over desired destination cell and mousing over the Paste icons:

Paste Special Linked Picture

Tip: If you like keyboard shortcuts, press: ALT-H-V-I to paste a linked image.

Paste Special Linked Picture and Camera Tool Restrictions

Not too many downsides to using the Camera tool but here’s some cautions:

  • Some users say that it does not work with Tables; that the data must be converted to a range, but I have not experienced that with newer versions. The table copies, pastes and updates.
  • If using formulas such as IF function with Camera tool, you must use Named Ranges, rather than cell references to maintain the update connection.

Note: The Copy command in the Clipboard group on the Home tab, contains a Copy as Picture… option. This is handy but know that it pastes an image but not a link. Use when you just want a snapshot of your data.

Grab the post for Part 1 hereCamera Tool Part 1

See – don’t even have to be a photographer! Take some pictures and play with dashboards. What did you create?

Use the Excel Camera Tool to Combine Objects from Several Workbooks

Uses for the Camera Tool

There is a little known spiffy tool that has been available for a long time in Excel which allows you to take screenshots of data from multiple worksheets or workbooks and paste them in a separate workbook as objects with links to their original locations. This can include ranges, tables or charts. Even better, if the original object updates, so does the linked object on your “collector” worksheet.

Here’s a short video to give you a quick overview of what the Excel Camera tool can do for capturing data and objects from different areas:

Collect desired data on one worksheet

For instance, you want to know the sales or prices from workbooks saved in different locations. You have figures for sales reps and a corresponding chart in Workbook A, and expenses that you want to track in Workbook B. Someone else may be updating the data but because anything you copy and paste with the Camera tool is pasted as a linked picture, any changes made to original data will auto update your screenshots.

Print collected objects on one page

This is also a great way to collect different areas of the same or separate worksheets or workbooks for printing a variety of data on one page as you can resize and move the different objects anywhere on the worksheet.

Add Camera Tool to Quick Access Toolbar (QAT)

First things first…The Camera is not available on the Ribbon by default so needs to be added to the QAT with the Customize Quick Access toolbar command:

  • Click the drop down arrow at end of Quick Access Toolbar. Choose More Commands… (or right click on the Ribbon)
  • Choose All Commands from drop down arrow next to Choose Commands from:
  • Scroll down the alphabetical list and click Camera.
  • Click Add button to add to Quick Access toolbar.
  • Click OK button at bottom of dialog box to place the Camera icon at the end of the QAT.

How to Capture a Screenshot

Select a range, table or chart to activate the Camera tool. Note, if selecting a chart, select the cell above the top, left border of the chart and draw around it. When you release the mouse, the “marching ants” will be around the object as if you had used the Copy command:

  • Go to your destination; usually in another worksheet or workbook.
  • Click in the desired cell location and the linked picture will auto insert.
  • Move and size the object(s) as desired.

Similar Feature with Paste Special Linked Picture

The newer versions of Excel (2007+) have another feature which behaves the same as the Camera tool – the Paste Special Linked Picture. I still prefer the Camera tool as just clicking on the desired destination cell pastes the linked image, all ready for sizing and relocating but both work.

The Camera tool can also be used to create Dashboards. We’ll cover that and some of the other options in an upcoming blog.

Take a picture and let me know what you think!

Want more ways to use the Camera? See Part 2: Camera Tool Part 2

Conditional Formatting for Clarity and Visual Impact in Excel

Light Up the Cells with Conditional Formatting!

Conditional Formatting is a great tool for instant, visual results based on values, text or formulas in one or more cells. This is accomplished by creating rules for each desired result. It can be as simple as formatting all cells based on their values (the default), which could be applying a color in cell(s) that are above or below a certain value, contain specific text or fall within certain dates as well answer more complex questions.

Conditional Formatting is located on the Home tab | Styles group:

You can also choose to have the results displayed as Data Bars, Color Scales or Icon Sets (arrows or star ratings) instead of one solid color.

More than one condition can be applied to the same range of cells. This example below is returning two different results with two different cell colors based on two different questions (AND requires both conditions to be true but OR allows for either condition to be true to apply the rule).

The formulas in the last two columns have returned Yes or No based on True or False results. Range Names have been created from the header row text to make for easy identification of the cell references in the formulas. Then Conditional Formatting rules have been applied to designate the color(s) based on that answer.



It is now very clear to see how many authors met none, one or both of the conditions.

Formatting Rules

There are four formatting rules applied here:

Rule Types

This is the Rule Type applied to the first rule for formatting only cells that contain “No” in the last two columns with Pink fill:

Each of the other three rules have their own Rule Description but all are based on Format only cells that contain (the second rule).

Note: You can include a formula directly in the condition by choosing the Rule Type: Use a formula to determine which cells to format and typing the formula in the Edit the Rule Description area.

Have you been using Conditional Formatting and, if so, which rule do you use the most? If not, I hope this post will encourage you to use this powerful feature. Thanks for reading!