Mynda's Excel Dashboard Webinar

Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

May 10, 2016

Hi fellow Excel Addict,

I want to thank everyone for their prayers for my fellow Canadians and Newfoundlanders affected by the Fort McMurray wildfires last week. Things there are pretty bad but could have been so much worse. It's a real eye-opening example of how one's life can be turned upside down in a single day.

Just a quick note on last Thursdays tip, 'Copy Multiple Ranges of Data and Paste Them As One Single Range'. An important point that I didn't include in the tip
is that pasting from the Clipboard pane doesn't paste formulas. All data pasted using the Clipboard pane is pasted as fixed values.  The online version of the tip has been updated with this information.

If you have a favourite tip that you'd like to share, or a problem that you've been trying to solve, that I could possibly include in my newsletter, please send it along to me.

Wishing you an AWESOME week,
Francis Hayes (The Excel Addict)

Francis Hayes ( Quote of the Day

Appreciate what you HAVE
before time makes you
appreciate what you HAD

-- Unknown --

If you missed my last newsletter, you can click here to view it online.

Excel in Seconds with

Show Individual Cell Values for a Range in a Formula

You may already know that you can select just part of a formula and press the F9 key to display its calculated value.

Evaluate Part Of Formula With F9 Keyboard Shortcut in Microsoft Excel 2007 2010 2013 2016 365
What you may not know is that if you highlight a reference to range of cells in the Formula Bar and press the F9 key, the individual values for all of those cells will be shown. This is sometimes helpful when creating or troubleshooting your formulas.

In this example, for the formula =SUM(B3:E3) , if you drag to select B3:E3 in the Formula Bar and press the F9 key, you will see 

Display Individual Cell Values For Range In Formula Bar in Microsoft Excel 2007 2010 2013 2016 365

This also works if you edit your formulas within the cell (i.e. double-click).
Edit Formula With F9 In Cell in Microsoft Excel 2007 2010 2013 2016 365

When you're done, press the Esc key or click the X (Cancel) button on the Formula Bar to restore your original formula. If you accidentally press Enter before restoring your formula, you can click the Undo command on the Quick Access Toolbar or press CTRL+Z to get your original formula back.

Formula limited to 8,192 characters
This tip is more practical for smaller ranges as displaying a large number of values in the formula bar would be difficult to evaluate. However, if you do try this on a larger range, there is a limit to how big your formula can be. If the length of the resulting formula with values instead of cell references is more than 8,192 characters, you will receive an error message.

Formula Too Long Error Message in Microsoft Excel 2007 2010 2013 2016 365

Expand or collapse Formula Bar
If your formula is too long to display entirely, you can expand the the Formula Bar by (a) pressing CTRL+SHIFT+U or (b) dragging down the bottom edge of the Formula bar or (c) clicking the arrow on the right edge of the Formula Bar.

Keyboard Shortcut To Expand Or Collapse Formula Bar in Microsoft Excel 2007 2010 2013 2016 365

Thanks for supporting this newsletter and website

Mynda's Excel Dashboard Webinar
FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

Hospitals Are #NotATarget

Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630