 |
|
| TheExcelAddict.com | |
| October 23, 2019 | |
| Hi fellow Excel Addict, | |
| For
the past several months I have been
working on a remodeling project at a rental property I
own an hour's drive from my home. It's has taken much longer than expected, however, the end is in sight. Hopefully in another 2-3 weeks I will be finished that project and have more time to start publishing my newsletters on a regular schedule.  In the meantime, in my newsletter, I will be sharing with you some of my favorite tips from past newsletters. Maybe you missed one or maybe you've forgotten one. If I share one you are already using — all the better. You will have been saving yourself lots of time all along. I hope you like this week's 'Excel in Seconds' tip. If you do, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter. Again, I apologize for the inconsistent publishing schedule of my newsletters but I hope to be back on track very soon. Thanks again for your patience. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Show Formulas And
Results Simultaneously |
|
You may already know that the trick for toggling between displaying formulas and their results in a worksheet is CTRL+`. The grave accent (`) key is typically found next to the number 1 key on most North American Windows keyboards.  Press CTRL+` (grave accent) and all cells containing formulas will show the formula rather than the result in the cell. You can press CTRL+` again to toggle the formulas display off.  In the past you may have been told NO. But I'm here to tell you "YES YOU CAN !". Option 1: FORMULATEXT One option is to use Excel's FORMULATEXT function which was introduced in Excel 2013. Enter this function in one cell to extract the 'formula as text' from another cell as in this example.... =FORMULATEXT(E3)
 Option 2: Side-by-Side Windows If you have an older version of Excel and can't use the FORMULATEXT function or you simply don't want to add more formulas to your worksheet, here is another option you can use to see all of your worksheet formulas AND their results for the same worksheet at the same time. 1) First press CTRL+Home to go to cell A1. The reason for doing this is, when you open a New Window, Excel automatically scrolls that new window to the top left corner and makes A1 the active cell. If A1 isn't the active cell in the original window, your rows and columns in both windows will be out of sync; 2) Next, open a new window (i.e basically a mirror of the current window) by clicking View, New Window (** see IMPORTANT NOTE in Step 8 below). At first, it appears that nothing happened. But take a closer look at the filename in the window header. What happened is that a new identical window has opened on top of the original window and the only visible difference is that a 2 has been appended to the filename in the window header. You can see also that a 1 has been appended to the filename in the original window. This has no effect on the actual filename;  3) Next, we arrange the two windows side by side. Although it may seem that choosing the Side by Side option here makes sense, that option will default to arranging the windows one on top of the other. If you want to arrange them side by side, from the View tab click Arrange All, Vertical, Windows of Active Workbook. You should now have two identical copies of your worksheet displayed side by side; 4) Now, from the View tab click View Side by Side and select the workbook window name in the Compare Side by Side dialog. Click OK. If Synchronous Scrolling isn't highlighted on the Ribbon (View tab), select it. Synchronous Scrolling is great for comparing documents line by line and scanning for differences; 5) Next, click the sheet where you want to display the formulas and press CTRL+' (grave accent) to toggle the display of formulas ON; 6) You should now be able to simultaneously scroll through your worksheet and see formulas in one window and their results in the other. If the windows are not in synch, click Synchronous Scrolling to temporarily turn it off, scroll to the top of each window, then click Synchronous Scrolling to turn it back on;  7) Note that any changes you make in one window will be shown in both windows; 8) IMPORTANT NOTE: When you are finished working with the new window, close the second window first (i.e. the one with - 2 appended to the workbook name in the Title bar). You will notice that the number (- 1) appended to the original window name disappears.   If you close the original window first then save changes from the second window, those original View settings may be lost. Note that this does not affect any of the data in the workbook. Changes to data in either of the windows will not be lost. 9) When you close the original window you will be prompted to save changes, if any. I hope you find this tip helpful for scanning and comparing your worksheets.  |
|
|
|
|
To share this tip with your friends and
|
|
 |
|
|
|
|
|
|
|
 |
|
| 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. 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 you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |