Displaying Excel Formulas

Normally when you enter a formula into Microsoft Excel you want to see the result of the formula, not the formula itself. However, a situation may arise where you need to see the formulas in a spreadsheet and not the answers to the formulas. This need usually occurs when you have to ‘debug’ a problem spreadsheet.

The easiest way to look at a formula is to move the cell pointer to a cell with a formula and look at the formula bar as shown in the next screen shot.

ShowingFormulas0

However, this method is only effective if you have a spreadsheet with very few formulas. When you are working with a large spreadsheet it may be difficult to to find each formula. The following spreadsheet, while not large, has a lot of formulas. In fact, only three of the cells displaying numbers are actual data entry cells. Everything else is a formula.

ShowingFormulas1

The easiest way to display formulas in Excel is to hold the CTRL key and press `. {The key to the left of the 1 key on the keyboard.} This toggles the display between showing formulas and showing results. Starting with the 2007 version of Excel Microsoft added an icon to show formulas. It is logically placed (surprisingly) on the Formulas tab on the ribbon. This icon is also a toggle: alternating between displaying formualas and results.

ShowingFormulas2

Using either method on the demonstration loan comparison spreadsheet displays all of the formulas, including an extra one in cell A10 that was not displayed due to the formatting applied to that cell.

ShowingFormulas3

Note: The keyboard technique also works for LibreOffice Calc.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Hide The Microsoft Office Ribbon

When Microsoft released Office 2007 it replaced the menus and toolbars that everyone knew and loved with the ribbon. According to Microsoft the ribbon was more logical then the menus/toolbars and easier to learn. While that may or may not be true, millions of Office users had learned where the commands were located, at least for those that they used on a regular basis. These people were actually less productive when using Office 2007 because they had to re-learn the command locations. Many people could not figure out how to preview or print a document because Microsoft had removed the File menu and had replaced it with the ‘Microsoft Office Button’.

Office2007Ribbon

Office 2010/2013 continues with the ribbon, however, Microsoft has mercifully killed the Microsoft Office Button and brought back the File menu, although it is now called ‘Backstage View’. As you can see in the following Excel 2010 example the Backstage View does most of what the old File menu did, along with additional features.

Office2010Ribbon

Depending on the size of your monitor and the screen resolution, the ribbon in either of these versions takes the equivalent of four or five rows of screen ‘real estate’. To power users who already knew the old menus, this was just another reason for hating the ribbon. What many users do not know is that you can turn the ribbon display off, while the functionality is available at the click of the mouse. (Although the following sample screen shots are from Excel 2010, the procedure is the same in the other Office programs for the 2013, 2010 and 2007 versions.

To turn off the display of the ribbon, double-click one of the tab names. The ribbon disappears, with only the tab names remaining.

Office2010Ribbon_Hidden

When you need to access any of the ribbon commands, click the appropriate tab name. The ribbon is temporarily re-displayed ‘over’ your document. [Note: that you cannot see rows 1 through 2 in the following screen capture.]

Office2010Ribbon_TempRedisplayed

The ribbon is then re-hidden after performing the chosen task.

To turn the ribbon display on, double-click one of the tab names.

You can also try this tip on any other programs you use that have adopted the ribbon concept. For example, this tip is known to work with the SnagIt 9 and CorpTax programs.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Using Excel Sparklines

Starting with its 2010 version Microsoft Excel has a feature called sparklines. Excel sparklines are small one-cell mini-charts that allow the user to quickly see:

  1. trends over time, such as sales by quarter
  2. the relationship between various components, such as sales by location for quarter 1, or
  3. win-lose scenarios similar to a comparison between fair market value for an investment compared to its purchase price.

[Read more…]

Word Keyboard Text Selection

One of the ironies of using computer software programs with a graphical interface is that you have to move at least one hand from the keyboard to the mouse in order accomplish some command or task. When using a word processing program most users immediately reach for their mouse to make a text selection in order to apply formatting properties. This is very inefficient considering that you will be moving your hand back to the keyboard to continue typing one you are finished formatting the selected text.

This article explains some of the methods to select text with the keyboard when using Microsoft Word. These keyboard shortcuts should work in Word versions starting with 2003. NOTE: The ‘+’ between the key names indicates that you have to press the keys at the same time.

Shift + End

  • Selects text from the cursor to the end of the line.
  • In this context ‘line’ equals row. It does not equal ‘sentence’.

Shift + Home

  • Selects text from the cursor to the beginning of the line.
  • This is the opposite of Shift + End

Shift + Page Down

  • Selects text from the cursor one screen down.
  • ‘Screen’ does not equal ‘page’.
  • Will select text across two pages.
  • If cursor is not at beginning of line, will select text to approximately same location (minus 1 character) in last line of the screen.

Shift + Page Up

  • This is the opposite of Shift + Page Down.

Shift + Right Arrow/Shift + Left Arrow

  • Extends selection one character to the right or left depending on the arrow key pressed.

Ctrl + Shift + Right Arrow

  • Selects from the cursor to the end of the word.
  • If cursor is within a word, the remainder of the word will be selected.
  • If the cursor is at the end of a word, the next ‘space’ will be selected.

Ctrl + Shift + Left Arrow

  • Selects from the cursor to the beginning of the word.
  • If the cursor is already at the beginning a a word, this shortcut will select the previous word.

Ctrl + Shift + Down Arrow

  • Selects from cursor to end of paragraph.
  • If the cursor is already at the end of a paragraph, this shortcut will select the next ‘space’.

Ctrl + Shift + Up Arrow

  • Selects from the cursor to the start of the paragraph.
  • If the cursor is already at the start of a paragraph, the previous paragraph will be selected.

Ctrl + Shift + End

  • Selects from the cursor to the end of the document.

Ctrl + Shift + Home

  • Selects from the cursor to the beginning of the document.

Ctrl + A

  • Selects the entire document.

Word also comes with another method for selecting text via the keyboard: Extend Mode. Extend Mode works through successive pressings of the function key F8.

  1. Turns on Extend Mode
  2. Selects the current or nearest word.
  3. Selects the sentence containing the selected word.
  4. Selects the paragraph containing the selected sentence.
  5. Selects the entire document.

You must press the Esc key to turn Extend Mode off.

There is also a ‘reverse’ Extend Mode that is accessed by successively pressing Shift + F8. This keystroke combination reduces the text selection from Everything → Paragraph → Sentence → Word → Nothing.


Bookmark and Share

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Fixing and/or Removing Excel Links

Linking Formulas are one of the most useful features of Microsoft Excel. These formulas allow you to share or ‘re-use’ data contained in one workbook with another workbook. The following is a textbook example of a linking formula: =[Book2]Sheet1!A3. In English this translates to “Show me the text and/or value that is being displayed in cell A3 on Sheet1 in the Excel file named Book2.” In a network environment the name of the file can include the full path name to the file, I:\HR\Budget\2014\[Book2]Sheet1!A3, for example.

What happens, however, if someone moves the file to a new location, renames the file or any portion of the path, or worse, deletes the file? The short answer is that Excel displays an error message that your linking formula no longer works. How you fix the problem depends on what was done.

Starting with the 2007 version Excel installs with automatic updating of linking formulas turned off by default. If you get the following error message when opening a workbook containing linking formulas you will know the setting for you machine.

Links_SecurityWarning

Clicking on the Options button will display the following dialog box.

Links_SecurityWarning2

Obviously you have to check the lower radio button and then the OK button if you want to use linking formulas.

Assuming that you have automatic updating turned on, the following dialog box will appear whenever there is a problem with a linking formula.

Links_BrokenLinkWarning

If you click the Continue button the workbook will open normally, but the linking formula will not update. The linking formula will remain intact. You may want to choose this option if you are temporarily disconnected from the network. Clicking the Edit Links button will display the normal Excel File Open dialog box. You can then point to the new location for the file, or the new file name. The linking formula will be adjusted to the new location and/or name, retaining the same cell address.

In the worse case scenario, the file with the source data has been irretrievably deleted. In this situation you want to break the link, otherwise Excel will persist in displaying the previous error message each time that the workbook containing the linking formula is opened. To begin the process of breaking the link click the Edit Links icon on the Data tab of the ribbon.

Links_OnRibbon

This will display the following dialog box.

Links_EditLinks

Click the Break Link button, which displays the following warning.

Links_BreakingLinksWarning

As the warning states, this will convert the formula to its current value, similar to using Paste Special==>Values. Should you need the linking formula you would then have to re-create it once the source workbook is re-created.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Expand Excel Formula Bar

Microsoft Excel’s formula bar is the area of the spreadsheet window that is above column letter headings, to the right of the current cell location indicator. It is the area highlighted in yellow in the following screen shot.

FormulaBar1

The formula bar in the above figure does not show anything because cell C6 does not contain anything (i.e. it is a blank cell). The formula bar will display the contents of a cell whenever the cell contains something. This ‘something’ is going to be either text, a value, or a formula. The formula bar in the next screen shot shows that cell C6 contains the SUM function adding a few cells. [People new to spreadsheet programs should be aware that cell C6 will display the answer to the formula, but that the cell actually contains a formula.]

FormulaBar2

What happens if a formula is so long that it takes up more space than the formula bar? In Excel 2003, the formula bar would automatically expand to display the entire formula as seen in the next screen shot. Notice that column letter headings are not obscured. This behavior could cause problems for a user trying to trouble-shoot a formula.

FormulaBar2b

Starting with Excel 2007 the formula bar will normally display one line of the formula bar, even if the formula extends to multiple lines. How does a user know that the entire formula is not being displayed? By the up/down arrowheads now being displayed toward the right end of the formula bar as seen in the next screen shot. Using the up/down arrowheads allows a user to scroll through a formula one line at a time.

FormulaBar3

The end most icon (that looks like a ‘v’) allows you to expand the formula bar to three lines as seen in the next screen shot. The ‘v’ icon flips 180 degrees to resemble the carat symbol (^) when the formula bar is expanded. The carat also moves above the up/down arrowheads. The formula bar does not expand beyond three lines. Clicking the carat returns the formula bar to the normal one line display. The expansion/contraction of the formula bar can also be achieved via the following keyboard shortcut: CTRL+SHIFT+U.

FormulaBar4

What if you have a formula longer than three lines? Rather than using the method from the last paragraph, you can manually resize the formula bar.

Move the mouse to the bottom of the formula bar until the mouse pointer becomes a double-headed arrow.

FormulaBar5

Then drag with the mouse to make the formula bar as large as you need.

FormulaBar5b

Being able to change the size of the formula bar is a vast improvement over Excel 2003. Not only does the formula bar expand, but the column letter headings continue to be displayed. This makes for much easier formula verification.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Excel Page Layout View

As its name implies, Microsoft Excel’s Normal view is the view that you normally see when working in Excel. The following is a screen shot of the normal view.

ExcelViews-Normal

Excel has also offered the Page Break Preview view. This view will show you how your rows and columns will be split across pages when the spreadsheet is printed. Notice the faint ‘Page x of y’ in the middle of each printed page. Also, the dialog box instructing you as to how you can relocate the page breaks will always appear unless you check the ‘Do not show this dialog box again’ check box.

ExcelViews-PageBreakPreview

Starting with Excel version 2007, Microsoft has also included a Page Layout View option. This view is similar to Page Break Preview, but more powerful. As you can see from the next screen shot that in addition to showing where your rows and columns will be split across pages, this view contains horizontal and vertical rulers for setting the margins.

ExcelViews-PageLayout1

This view also lets you directly enter a header (or footer) without having to go through the Page Setup dialog box. When you click the ‘Click to add header (or footer)’ area of the view, two things happen. First, the text disappears so that you can add your header (or footer). Second, the ‘Header and Footer Tools Design’ tab is added to the ribbon.

ExcelViews-PageLayout2

This toolbar lets you add some header (or footer) elements and change some of the other options. The following screen shot shows the effect of clicking on the File Path icon while the mouse cursor is in the center section of the header.

ExcelViews-PageLayout3

Once you get used to the Page Layout view, you may find it easier to use than the ‘old’ Page Setup dialog box.

Copyright 2014 – VBA Consultants Ltd

Customizing the Office 2010/2013 Ribbon

The biggest change in Microsoft Office 2007 was the replacement of the familiar menu/toolbar structure with ‘the ribbon’. This change was so radical that many corporations did not switch to Office 2007 in order to avoid the massive retraining costs. The most common complaint from users that had to switch to Office 2007 was “I can no longer find the commands that I used to know by heart.”

One of the useful features of Office 2010/2013 is the ability to modify the ribbon. By modifying the ribbon you can place the icons for your most used Office commands into a separate (i.e. new) tab, or you can add a group to an existing tab. You can even group the icons in a manner similar to the out-of-the-box ribbon. You also have the ability to stop the display of one or more of the built-in tabs.

[Read more…]

Excel’s Conditional Formatting

Microsoft Excel’s Conditional Formatting allows you to create rules whereby the format of one or more cells changes based on the data in your worksheet. You would want to do this so that you can focus on the items that need review or correction. Examples could be employees with excess overtime, departments with high absenteeism or injuries, products with a high number of returns, or salesmen failing to make sales goals. Starting with the 2007 version Excel has not only made it easier to apply conditional formatting, but it also has made more formatting options available.

[Read more…]

Customize the Office Quick Access Toolbar

The Quick Access Toolbar (QAT) was Microsoft’s attempt to appease users of Office 2003 for the replacement of the menus/toolbars with the Ribbon that debuted in Office 2007. The QAT allowed users to add their favorite command shortcuts to one location rather than having to search the ribbon for commands that were no longer in the expected location. The QAT is the series of icons that are (by default) displayed above the tabs of the Office ribbon. The default QAT for Word 2010 is shown below.

QAT1

To add the commands that you need to the QAT, click the drop-down arrow at the right end of the QAT and select More Commands from the menu. NOTE: Although our example is using Word, the procedure is the same for all Office applications that have the QAT.

QAT2

This will open the Word Options dialog box as seen in the next image. NOTE: this post uses images from Office 2010. The dialog boxes look slightly different in Office 2007.

QAT3

The left drop-down allows you to narrow the area from which you wish to choose a command. Use All Commands for a comprehensive list.

QAT5

The right drop-down allows you to customize the QAT for all documents, or just the current one. This could be very useful if a document that you use on a regular basis requires special commands.

QAT4

The basic procedure is to select a command from the left pane and move it to the right using the Add >> button. Do not to forget to add the Separator to add vertical bars to the QAT. Use the << Remove button to remove commands from the QAT. The up/down arrows along the right side of the dialog box can be used to arrange the icons left-to-right.

QAT6

The QAT will be look like the following after clicking the OK button on the previous dialog box.

QAT6a

You can go through the procedure again to make any additional modifications. You can also erase all of your customizations and start from scratch if you click the Reset button on the options dialog box.

QAT7

 

 

View VBA Consultants profile on LinkedIn

Copyright 2013 – VBA Consultants Ltd