Tag Archives: Excel

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:

=(B9+E9)*G9/F9

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.

=IF(AND(Years_Under_Contract<2,Number_of_Books_in_Print>4)=TRUE,”Yes”,”No”)

=IF(OR(Years_Under_Contract>5,Number_of_Books_in_Print>=10)=TRUE,”Yes”,”No”)

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!

Flash Fill in Excel 2013 and Above

Why You Need Flash Fill…

Flash Fill is a time-saving feature that reads patterns in adjacent columns and Lightening Flash
automatically fills the remaining cells in a column based on those patterns. It is useful when you need to join cells (Excel calls this Concatenate), or separate information in cells without wanting to write a cumbersome formula. You might already know the Text to Columns feature but this is faster and easier – Kind of like AutoFill on steroids!

Flash Fill should automatically be on by default but you can check here: File tab > Options> Advanced:


This amazing tool will make short work of many repetitive tasks, all without formulas (or the Text to Columns feature)! Just make sure you are working next to the column(s) you want to Flash Fill. Here’s just a few of the things it can do:

Splitting Input Data

You received a huge spreadsheet where someone has put first and last names 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…

Example: Full names are in Column A. Here’s the steps:

Insert:  two columns to right of Column A, and enter appropriate column header labels.

Type:  “Fred”, in appropriate cell ( B2 in example).

Press:  ENTER to go to cell below.

Type:  “Mary” (note that Excel has figured out what you are doing; displays the remainder of first names, and is awaiting your approval).

Press:  ENTER, and presto, all the rest of the first names are added!

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

List is now complete:

Just Want Last Names?

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.

Combining Data

You can do the reverse of splitting data when you have or receive a workbook with data that is in two columns and you would like combined into one.

Example: Fred is in Column A and Frump in Column B:

  • Insert a blank column to right of Column B.
  • Type the first instance the way you would like it displayed, i.e., Fred Frump or Frump, Fred.
  • Press ENTER and start to type the second example. Excel should display the preview for the balance of the column. Just press ENTER to complete.

You Mean Flash Fill Does More?

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, email addresses, dates, numbers and time:

If Flash Fill does not appear to work when entering the second example, you can activate it from the Ribbon: Data  tab > Flash Fill or keyboard with CTRL E. Just click in the second cell (ensure blank) and use the Ribbon command to fill down the column.

ALERT: Be aware that if you change source data, cells containing flash fill data will not update as there are no formulas involved.

There are many other ways to use Flash Fill. Now that you can save all that time entering data, what ways will you use it?

Reasons to Format Excel Spreadsheets with Cell Styles

Use Cell Styles to Format in Excel to save time and frustration and look smart doing it!

Here’s why you want to use Cell Styles to format Excel data:

  • Apply professional formatting to a worksheet in a flash.
  • Consistency across worksheets.
  • Built-in styles are labelled according to purpose for easy use.
  • Styles are customizable so you can edit existing styles or create your own to match your needs.
  • One click formatting.

Have you ever wondered how to format a spreadsheet in a hurry? Here’s a simple example of a worksheet with boring, unformatted data:

Name Region Q1 Q2 Q3 Q4 Total
Smith South 21223 17855 24855 21377 85310
Jones Southwest 23456 29550 24294 25335 102635
Wing North 19954 22600 19448 26450 88452
Baker South 17564 25439 32944 24000 99947
Folsom North 28543 28540 24400 28550 110033
Range Northwest 19534 30558 21844 19605 91541
McKnight West 20585 29667 27595 13605 91452
Crump South 24957 31322 15330 27550 99159
Rogers North 30332 27407 26440 24007 108186
Morris East 25395 27700 16500 27500 97095
Rollins South 35822 31854 24384 31005 123065
Jefferson Southwest 20949 23100 21774 27550 93373

But now you get a call to send it to a co-worker or the boss or it has to be ready for a presentation. Using individual formatting tools, it can take longer to format than it took to create it!

Format the Column headers and Data Content

Want to look smart really fast? Here’s the fix…First, I want to format the header row:

Select the header row in the worksheet

On the Home tab, in the Styles group, click Cell Styles icon:

If you have a larger screen and/or higher resolution, you may see several cell styles already displayed. See all styles by clicking the More arrow button at the bottom of the scroll bar for the group.

This box below will display with lots of options that are labelled for specific uses but you can use them for any purpose you choose. Roll your mouse over the sections and because they are live preview, you can see the results before you actually choose the option. I want Heading 1 for my header row, so I point and click to apply it.

I’ve selected Heading 1 under the Titles and Headings section:

Next select the body of the spreadsheet:

Click Cell Styles again, and choose whatever style you think would look best for your data. (If you are using Input Style, don’t include the Total column as you’ll probably want to use the Calculate Style for that to indicate formulas).

Total Row Style

If you have a total row, quickly make your totals stand out with the Total Style:

Data and Model Section

A way to alert users about which data they can edit is to use the Input and Calculation styles under the Data and Model section. It can’t stop them from overwriting formulas, etc., but it does signify the ranges that they should not edit. If you don’t like the default colors, you can change them. (See below)

Remove Styles

So, you’ve been playing and got carried away and now your data looks like a kindergarten project! Don’t worry if you goof and want to remove a style. Just select that range of cells; go back to the Cell Styles icon to the Style group and click on Normal to reset the cells to your default font style and size. (Located in the upper left of the first section, Good, Bad
and
Neutral).

Customize/Create Styles for Text

You can modify any style or leave the original styles and create a duplicate for your new style. I would recommend the latter. Here’s how to make changes in font, color, etc. for any style:

Example here is for Heading 1:

  • Click the Cell Styles icon (Home tab, Styles group).
  • Right click over the style you want to change.
  • Choose Duplicate (or Modify if you want to edit the original style) to display the Style box:

  • Change the name.
  • Click the Format… button and make desired changes. (Ensure Font tab is selected).
  • Click OK, OK.

Changes to Numerical Data Styles

If you want to change the Input or Calculation or any other numerical styles to add number formatting as well, the process is the same except you:

  • Right click over the style name.
  • Choose Duplicate.
  • Type in a new style name.
  • Click on the Number tab in the Format Cells dialog box.
  • Click on Currency in the Category pane.
  • Choose if you want the style to always display 2 decimal places or change to 0 if you want that for the style.
  • Make any other desired changes to Font, Alignment, etc., using the tabs at top.
  • Click OK, OK.

Apply Customized Styles

This can be a little confusing after modifying a style as it looks like nothing happened. The new style is not applied to your selected data. because you have created the style but not yet applied it.

When you customize or create new styles, Excel adds a Custom section to the top of the Cell Styles list. Just select desired data, click Cell Styles and click on your customized style. I added Currency formatting to the Input Style but left the background cell color; applied Currency formatting for the Calculate Style but changed to red color so it would be obvious not to enter data in those cells:

I selected the quarterly data to apply my customized Input Style, and then selected the Total column to apply my new Calculation Style. If there is also a Totals row, I would select them both first, and then go to Cell Styles and click my customized style:

Create your own styles and save a bundle of time while looking darn professional as well!

Let me know how it works for you…

Quick Navigation in Excel Worksheets

Go There in a Flash in Excel

You inherited the biggest spreadsheet on the planet and have to scroll and troll to ranges miles away from your current screen! Don’t despair, Excel has several tools to get you there quickly…

The Go To Dialog Box

You can jump to any area of your worksheet with this handy command. Using the Ribbon, Home tab, Editing group, Find & Select drop down arrow, and select Go To… Just type in a cell reference, click OK or press ENTER, and you are there!

OK, that might beat scrolling but not by much, so here’s the corresponding shortcut:

Press CTRL G or F5 function key to auto display the Go To Dialog Box and it is ready for your entry. It also remembers your previous cell location(s).

The Name Box

Want the quick mouse navigation trick? The Name Box is your ticket! Just click in the box and type the cell reference (such as T44456); press ENTER and you are there. (Unfortunately, there is no keyboard shortcut for the actual Name Box but you could use F5 to display the Go To box, type the cell reference and press ENTER).

keyboard angled 2 x 3
Where did I put that?

Range Names

The amazing Name Box is multi-talented. We saw we can highlight large ranges of cells and also jump to a faraway cell location just by typing the cell reference(s) in the Name Box. But what if you are short on sleep and can’t remember the cell reference(s)? No problem. There is a reason it is called the “Name Box”. All you have to do is find that cell once, select it and give it a name that makes sense to you, like Sales or Profit, etc.

Steps:

  • Navigate to desired location
  • Click on desired cell (or range of cells)
  • Click in the Name Box and type a name, i.e., Sales
  • Press ENTER

To return to that location any time, just click the drop-down arrow on the Name Box and select the name you gave it, and you are magically transported there!

Important things to know when naming a cell:

  • Range Names must start with a letter but can contain numbers.
  • You can use more than one word to name a range but it cannot contain spaces.
  • Capitalize the separate words, like TotalSales, or the underscore can be used to represent the space, i.e., Total_Sales.

Range Names are ABSOLUTE by default, meaning the name will always refer to the cell even if the content is moved. And, even more impressive, Range Names can also be used in formulas instead of cell references. That Excel is one smart cookie!