Tutorial Excel : Show Formulas Instead of Results

You can more easily review and troubleshoot a worksheet by changing its display to show the formulas in each cell instead of those formulas’ results. If you want to check the formula for a particular cell, you cannot examine the formula just by looking at the cell because Excel displays the result of the formula instead of the formula. You must click the cell so that Excel displays the formula in the Formula bar.

That is fine for a single cell, but what if you need to check all the formulas in a particular worksheet? You could simply click each cell that contains a formula, but that is impractical in a sheet with dozens of formulas, and it does not enable you to easily compare one formula with another to look for errors or anomalies. Instead, you can change the worksheet view to display the formulas in each cell rather than the formula results.

How to Show Formulas Instead of Results

1. Switch to the work sheet that contains the formulas you want to display.
2. Click the File tab.

3. Click Options.

The Excel Options dialog box appears.
4. Click Advanced.
5. Click to select the Show Formulas In Cells Instead of Their Calculated Results option.
6. Click OK.

● Excel displays theformulas instead of theirresults.
Note: You can also toggle thedisplay between formulas andresults by pressing Ctrl+`.

Try This!

The technique you learned in this section applies only to the current worksheet. If you
want to view the formulas in every sheet in a workbook, run the following VBA macro:
Sub ToggleFormulasAndResults()
   Dim win As Window
   Dim wv As WorksheetView
   For Each win In ActiveWorkbook.Windows
      For Each wv In win.SheetViews
         wv.DisplayFormulas = Not wv.DisplayFormulas
      Next ‘wv
   Next ‘win
End Sub
See Chapter 11 to learn how to add and run a VBA macro in Excel.

Read More!

Tutorial Excel : Paste a Formula’s Result

You can control the output that a copied formula displays by pasting the formula’s result rather than the actual formula. After you copy a formula that uses relative cell references, when you paste the formula, Excel automatically adjusts the cell references. For example, if the destination cell is one row down from the original cell, Excel adds 1 to the value of each row reference in the formula. This is usually welcome behavior because it helps you to repeat similar formulas without having to retype them.

However, this automatic adjustment of cell references means that you always end up with a different formula after you paste the original. One way to avoid this is to use absolute cell references (see the section, “Use Absolute Cell References in a Formula”). Alternatively, if you are only interested in the formula result, you can paste the copied formula as a value.

How to Paste Formula Result:

1. Select the cell containing the formula you want to copy.
Note: This task uses a single cell, but the technique also works for arange of cells. 

2. Click the Home tab.
3. Click the Copy icon.
Note: You can also copy the selected cell by pressing Ctrl+C. 

4. Click the cell where you want to paste the formula value.
5. Click the Paste drop-down arrow.
6. Click a Paste Values option.

● Excel pastes just the value of the formula, not the actual formula.

More Options!

If the copied cell has a number format applied, or any other cell formatting, when you paste the result using the Paste Values command, Excel does not copy the formatting to the destination cell. To include the original number format in the pasted cell, click the Paste drop-down arrow and click Values & Number Formatting, instead; to transfer all of the original cell formatting, click Values & Source Formatting.

Try This! 

If you are interested in displaying a formula result in a particular cell, you can paste just the value of that formula, but that pasted value will be incorrect if the inputs to the formula change in the future. To ensure that a particular cell always displays the current formula result, select the destination cell, press the equals key (=), click the original cell, and then press Enter. This simple formula tells Excel to always display the value of the original cell’s formula.

Read More!

Tips Excel : Create Custom Date and Time Formats

You can enhance your worksheet display of dates and times by creating your own custom date and time formatting. Date and time formats determine how Excel displays data values and time values in a range. For a date, the date format determines whether Excel displays the value with the year, month, date, or all three, and whether you see short values such as Fri or long values such as Friday.

For a time, the time format determines whether Excel displays the value with the hour, minute, second, or all three. Although the built-in date and time formats are fine for most applications, you might need to create your own custom formats. For example, you might want to display just the day of the week (for example, Friday). To do this, you can create custom date and time formats. You can do so either by editing an existing format or by creating your own format. The formatting syntax and symbols are explained in the Tip.

How to Custom Date Format  Excel

1 Select the range you want to format.
2 Click the Home tab.
3 In the Number group, click the dialog box.

The Format Cells dialogbox appears with theNumber tab selected.
● If you want to base your custom format on an existing format, click either the Date or Time category and then click the format.
4. Click Custom.

5. Type the symbols and text that define your custom format.
6. Click OK.

● Excel applies the custom format.

Customize It! 

Use the symbols in the following table to build your custom date and time formats.
Symbol Description
d 
Displays a day number without a leading zero (1–31).
dd 
Displays a day number with a leading zero (01–31).
ddd 
Displays a three-letter day abbreviation (Mon).
dddd 
Displays a full day name (Monday).
m 
Displays a month number without a leading zero (1–12).
mm 
Displays a month number with a leading zero (01–12).
mmm 
Displays a three-letter month abbreviation (Aug).
mmmm 
Displays a full month name (August).
yy 
Displays a two-digit year (00–99).
yyyy 
Displays a full year (1900–2078).
h 
Displays an hour without a leading zero (0–24).
hh
Displays an hour with a leading zero (00–24).
m 
Displays a minute without a leading zero (0–59).
mm 
Displays a minute with a leading zero (00–59).
s 
Displays a second without a leading zero (0–59).
ss 
Displays a second with a leading zero (00–59).

Read More!

Tips Excel : Create Custom Numeric Formats

You are not limited to predefined numeric formats in Excel; you can create a custom cell format that displays information just how you want it. The predefined numeric formats give you a lot of control over how your numbers are displayed, but they have their limitations. For example, no built-in format enables you to display a number such as 0.5 without the leading zero, or to display temperatures using, say, the degree symbol. To overcome these and other limitations, you need to create your own custom numeric formats.

You can create custom numeric formats either by editing an existing format or by creating your own format. The formatting syntax and symbols are explained in the Tip. Every Excel numeric format, whether built-in or customized, has the following syntax: positive;negative;zero;text The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed.

How To Custom Numeric Format 

1. Select the range you want to format.
2. Click the Home tab.
3. In the Number group, click the dialog box launcher icon. 

The Format Cells dialogbox appears with theNumber tab selected.
● If you want to base your custom format on an existing format, you can click the category and then click the format.
4. Click Custom.

5. Type the symbols and text that define your custom format.
6. Click OK.

Excel applies the custom format.
 

Customize It!

Use the symbols listed in the following table to build your custom numeric formats.
Symbols for Custom Numeric Formats
# 
Holds a place for a digit and displays the digit exactly as typed. Excel displays nothing if no number is entered.
0 
Holds a place for a digit and displays the digit exactly as typed. Excel displays 0 if no number is entered.
? 
Holds a place for a digit and displays the digit exactly as typed. Excel displays a space if no number is entered.
. (period)
 Sets the location of the decimal point.
, (comma)
Sets the location of the thousands separator. Excel marks only the location of the first thousand.
/ (forward slash)
 Sets the location of the fraction separator.
% 
Multiplies the number by 100 (for display only) and adds the percent (%) character.

Read More!

Excel : Convert a Workbook to a PDF File

If you want to share an Excel workbook with another person who does not have Excel, you can save that workbook as a Portable Document Format (PDF) file. Microsoft Office is by far the most popular productivity suite, and Microsoft Excel is by far the most popular spreadsheet program. However, although this means that many people have Excel, it does not mean that everyone does. 

So if you want a non-Excel user to see your Excel data and results, you must find some way of sharing your workbook with that person. One easy way to do this is by using a PDF file, which uses a near-universal file format that displays documents exactly as they appear in the original application, but can be configured to prevent people from making changes to the document. Most people have the Adobe Acrobat PDF reader on their system, and a free version is easily obtained online from adobe.com.

Excel Menu

1. Open the workbook you want to convert to a PDF.
2. Click the File tab.

3. Click Save & Send.
4. Click Create PDF/XPS Document.
5. Click Create PDF/XPS.

The Publish as PDF or XPS dialog box appears.

6. Choose a location for the file.
7. Type a name for the file.
8. Make sure the Save as Type drop-down list shows PDF.

9. Click to select the Standard option.

If you will be sharing the PDF file online, you can create a smaller file by clicking to select the Minimum Size option, instead.
10. Click Publish.
Excel publishes the file as a PDF.

More Options!

By default, Excel publishes only the current worksheet to the PDF. If you want to publish the entire workbook, instead, follow steps 1 to 9 to open the Publish as PDF or XPS dialog box and set up the file. Click the Options button to open the Options dialog box, click to select the Entire Workbook option, and then click OK.

Did You Know? 

One problem with PDF is that it is a proprietary standard (it is owned by Adobe Systems) and you may prefer to use a format based on open standards. That is the idea behind the XML Paper Specification, or XPS. XPS uses XML (eXtensible Markup Language) for the document syntax and the ZIP format for the document container file, so it is based on open and available technologies. In the Publish as PDF or XPS dialog box, click the Save as Type drop-down list and then click XPS Document.

Read More!

Repair a Corrupted Workbook File Excel

If you have an Excel workbook that you can no longer open because the file has become corrupted, Excel offers a repair option that should enable you to fix the file. Excel workbooks rarely have problems and they generally open successfully. However, a hard disk error or memory error could create a problem that corrupts the file.
When that happens and you try to open the workbook, Excel displays an error message telling you either that it does not recognize the file format or that the file is corrupted. Whatever the cause, it is important that you do not lose any data, so Excel offers an Open and Repair command that first attempts to repair the file, and then to open the repaired workbook in Excel.

Excel Menu

1. Click the File tab.
2. Click Open. 
The Open dialog box appears.
3. Click the workbook you want to repair.
4. Click the Open drop-down list.

5. Click Open and Repair.

6. In the dialog box that appears, click Repair.

7. Excel repairs and then opens the file.

More Options!

 If Excel cannot repair the workbook, you may still be able to save the workbook’s data. Follow steps 1 to 5 to select the Open and Repair command. In the dialog box that appears, click Extract Data. In the dialog box that appears, click Recover Formulas if you want Excel to try and recover the workbook’s formulas. If that does not work, repeat Steps 1 to 5, click Extract Data, and then click Convert to Values, instead; this tells Excel to convert all the formulas to their results. After Excel repairs the file, click Close.

Another option you have to recover some or all of your work is to open a previous version of the workbook, if one exists. Follow steps 1 to 4 to display the Open drop-down list, and then click Show Previous Versions. Excel displays a list of the available versions of the workbook. Click the version you want to use — usually the most recent version that you believe is not corrupted — and then click Open.

Read More!

Increase the Number of Recent Documents MS Excel 2010

You can make it easier to find the workbooks you use most often by increasing the number of files that Excel displays on its Recent list. When you click the File tab and then click Recent, Excel displays a list of the workbooks that you have used most recently, and clicking an item in the list opens that workbook. The Recent list is therefore a quick way to open a file, but only if the workbook you want appears in that list. 

To improve the chances that a workbook appears in the Recent list, you can increase the number of files that Excel displays. The default is 22, but you can specify a number as high as 50. If you run your PC at a relatively low resolution, such as 1024 x 768, Excel only has space to display the first few recent documents, and it adds a scroll bar to the list so you can navigate the rest. However, having to scroll to a recent document just slows you down, so you can reduce the number of recent documents to about 15, or whatever number prevents the scroll bar from appearing.

Excel Menu

1. Click the File tab.

2 Click Options.

The Excel Options dialogbox appears.

1. Click Advanced.

2. Use the Show  This Number of Recent Documents spin box tospecify the number of recent workbooks you want to display.

3. Click OK.The next time you click the File tab and then click Recent, you see the number of recent
workbooks that you specified.

More Options! 

Excel 2010 gives you a few options for manipulating the list of recent documents. For example, if a workbook that you use only rarely appears on the list, you should remove it to make space for a file that you use often. Right-click the workbook and then click Remove From List. If you want a particular workbook to always appear on the list, click the pin icon to the right of the file.

Did You Know? 

If you are running Excel 2010 on Windows 7, you can take advantage of jump lists to open recent Excel workbooks. Pin the Excel icon to the taskbar as described in Chapter 1. You can then right-click the icon to access the recent workbooks. You can also pin items to this list by clicking the pin icon or by dragging and dropping a workbook onto the Excel icon.

Read More!

Export Ribbon Customizations to a File Ms Excel 2010

You can make it easy to apply Ribbon and Quick Access Toolbar customizations on another computer by exporting your own customizations to a file. Customizing the Ribbon or the Quick Access Toolbar is not a difficult process, but it can be time-consuming, particularly if you want to make a substantial number of changes.

If you use Excel 2010 on another computer, it is likely that you will want to have the same customizations on the other computer so that you are dealing with a consistent interface no matter where you do your spreadsheet work. Rather than wasting valuable time repeating the same customization steps on the other computer, you can export your customizations to a file. You can then import that file on the other computer, and Excel automatically applies the customizations for you.

1. Right-click any part of the Ribbon.
2. Click Customize the Ribbon.
3. The Excel Options dialog box appears.
• Excel automatically displays the Customize Ribbon tab.
4. Click Import/Export.
5. Click Export All Ribbon and Quick Access Toolbar 
• Customizations. The File Save dialog box appears.
6. Choose a location for the customization file.
7. Type a name for the file.
8. Click Save. Excel saves the customizations to the file.
9. Click OK.

To apply the Ribbon and Quick Access Toolbar customizations on another computer running Excel 2010,

you need to import the customization file that you exported by following the steps in this section. Note, however, that importing a customization file replaces any existing customizations that you have created. On the computer you are customizing, right-click any part of the Ribbon, and then click Customize the Ribbon to open the Excel Options dialog box with the Customize Ribbon tab displayed. Click the Import/Export drop-down arrow and then click Import Customization File. In the File Open dialog box, locate and then click the customization file, and then click Open. When Excel asks you to confirm that all of your existing customizations will be replaced, click Yes and then click OK. Excel applies the Ribbon and Quick Access Toolbar customizations.

Read More!

Customize the Ribbon Microsoft Excel 2010

You can improve your Excel productivity by customizing the Ribbon with extra commands that you use frequently. Keep in mind that you cannot modify any of the default tabs and groups in Excel, other than hiding tabs you do not use.
 Instead, you customize the Ribbon by adding a new group to an existing tab, and then adding one or more commands to the new group. Alternatively, you can add a new tab to the Ribbon, add your own groups to that tab, and then add your commands.

Display the Customize Ribbon Tab

1. Right-click any part of the Ribbon.
   
2. Click Customize the Ribbon.

The Excel Options dialog box appears.

• Excel automatically displays the Customize Ribbon tab.
• Use these lists to choose the commands you want to add.
• These lists show the existing tabs and
  
  groups.
• To display a tab’s groups, you can click the tab’s plus sign (+).

Add a New Group

1. Click the tab you want to customize.
2. Click New Group.
3. Excel adds the group.
4. Click Rename.

The Rename dialog box appears.

1. Type a name for the group.
2. Click OK. Excel adds the new group to the tab.
   

More Options! You can get more space on the Ribbon and reduce clutter by removing any tabs you do not use. For example, if you do not use the Excel reviewing tools, then you might prefer to hide the Review tab to reduce the number of tabs you see on-screen. In the list of tabs that appears below the Customize the Ribbon dropdown list, deselect the check box beside any tab you want to hide.

Try This! You can change the order in which the tabs appear in the Ribbon. For example, if you use the tools in the Data tab more often than those in the Home tab, then you can move the Data tab to be the first tab in the Ribbon. Use the up and down arrow buttons that appear to the right of the tab list to modify the order. You can also use these buttons to modify the order of the groups within any tab.

Although you will mostly prefer to add one or more custom groups to the default Excel tabs, this is not always convenient because it reduces the amount of space available to the other groups in the tab. This can cause the buttons to appear cluttered, making it harder to find the button you need.
In such cases, a better customization method is to create your own tabs and populate them with custom groups and commands. You can also export your Ribbon customizations to a file so that other people can import the same customizations. For more information, see the section, “Export Ribbon Customizations to a File.”

Add a New Tab

1. In the Customize Ribbon tab of the Excel Options dialog box, click New Tab.
• Excel adds the tab.
• Excel adds a new group within the tab.
  
2. Click the new tab.
3. Click Rename. The Rename dialog box appears.
4. Type the name you want to use.
5. Click OK.
6. Repeat steps 3 to 5 to rename the new group.

Add a Command

1. Click the Choose Commands From dropdown arrow.
2. Click the command category you want to use.
3. Click the command you want to add.
4. Click the custom group you want to use.
5. Click Add.
• Excel adds the command.
• To remove a custom command, click it and then click Remove.
6. Click OK.
• Excel adds the new tab or group, as well as the new command, to the Ribbon.

Try This! You can also customize the tabs that appear only when you select an Excel object. Excel calls these tool tabs, and you can add custom groups and commands to any tool tab. Right-click any part of the Ribbon, and then click Customize the Ribbon to display the Excel Options dialog box with the Customize Ribbon tab displayed. Click the Customize the Ribbon list and then click Tool Tabs. Click the tab you want to add, and then follow the steps in this section to customize it.

Right-click any part of the Ribbon, and then click Customize the Ribbon; the Excel Options dialog box appears with the Customize Ribbon tab displayed. To restore a tab, click the tab, click Restore Defaults, and then click Restore Only Selected Ribbon Tab. To remove all customizations, click Restore Defaults and then click Restore All Ribbon Tabs and Quick Access Toolbar Customizations.

Read More!

Customize the Quick Access Toolbar Excel 2010

You can make Excel easier to use by customizing the Quick Access Toolbar to include the Excel commands you use most often. Because you launch Quick Access Toolbar buttons with a single click, adding your favorite commands to the toolbar saves you time.

By default, the Quick Access Toolbar contains three buttons — Save, Undo, and Redo — but you can add any of hundreds of Excel commands. In a default Excel configuration, the Quick Access Toolbar appears above the Ribbon as part of the Excel title bar. However, this position only allows you to add a few buttons, as there is only so much space in the title bar.

To get much more space to add buttons, you should move the Quick Access Toolbar below the Ribbon. You can also export your Quick Access Toolbar customizations to a file so that other people can import the same customizations. For more information, see the section, “Export Ribbon Customizations to a File.”

1. Click the Customize Quick Access Toolbar button.
   
   
   
2. If you see the command you want, you can click it; Excel adds the button for that command to the Quick Access Toolbar, and you can skip the rest of the steps in this section.
3. Click More Commands. The Excel Options dialog 3 box appears.
4. Excel automatically displays the Quick Access Toolbar tab. 3 Click the Choose Commands From drop-down arrow.
5. Click the command category you want to use. 
6. Click the command you want to add.
7. Click Add.
   ● Excel adds the command.
   ● To remove a  command,you can click it and then click Remove.
8. Click OK.
   
   
9. Excel adds a button for the command to the Quick Access Toolbar. 

Read More!