Notes from the Help Desk…

All Office 2010 applications now include a screen capture utility to help you quickly capture any area of the desktop screen.

The tool will automatically take screenshots of all open applications on your desktop (that are not minimized), and let you insert them directly into your document.

Inserting a Full Screen Screenshot

To insert a full screen screenshot:

1. Click Screenshot in the Illustrations group on the Insert tab of the Ribbon.
2. Word will display thumbnails of all open applications that are not minimized.



3. Click the thumbnail you want to insert the screenshot in your document at the insertion point.

To select a cell in Excel, you have two basic methods: RANGE and CELLS:

Range ("A1").Select

Range("RangeName").Select

Cells(3, 4).Select   'Selects Row 3, Column 4, i.e. cell D3

Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it  with a loop:

For i = 1 to 10
     Cells(i, 1).value = i   ' fill A1 through A10 with the value of i
Next i

Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster than selecting the cell, changing the value, selecting the next cell, etc. If you are watching the sheet, the values simply appear.

Slicers are a new feature in Excel 2010 that let you add a snapshot view of a pivot table to a worksheet.  Slicers are like visual filters.  You may want to use a slicer when you only need to display a section of a pivot table.

For example, the pivot table below shows conference registrations broken down by city and state.  You can add slicers to this pivot table to display the data from one state or from only one town from one state.

Sparklines are miniature graphs that fit inside single worksheet cells.  They can show you trends or changes that may not be easily noticed by viewing the values in the spreadsheet.

Although Sparklines can be located in any cell, they are most effective when placed next to the data to which they refer.

Note that Sparklines are only available in .XLSX or .XLSM files. The option is greyed out in .XLS files.

One of the techniques I have found very useful in writing Excel VBA code is to make the cell, row, and column references public constants rather than hard coding them.

Take a reference to a row and column:

   cells(4,27).value

If row 4 is the first data row and column 27 is the last column in the list, it is more useful to make them constants and refer to the constant:

   Public Const intFirstDataRow As Integer = 4
   Public Const intLastRow As Integer = 27

And then make the above reference:

    cells(intFirstDataRow, intLastRow).value

This way, when you add a couple of columns or move the first data row down, you change the constant once and the code all works the first time.

Searching and replacing 27 with 31 is fraught with problems since it is too easy to change values or longer numbers (12734 to 13134) inadvertently.

Word 2010 includes a new Paste feature – the Paste Options Gallery, which puts a number of options for pasting at your fingertips.

As in earlier versions of Word, when you paste, you will see a Smart Tag that lets you select a variety of paste options.

As you hover over the options in the Paste Options gallery, the Live Preview feature temporarily displays your data as it will look if you select that option.

Viewing the Paste Options Gallery

To view the Paste Options gallery:

1. Press the CTRL key or click the Smart Tag.
2. Word will display the Paste Options Gallery:
3. Hover over an option to preview your pasted data.
4. Click an option to paste the data into your spreadsheet.

In Office 2007, the Ribbon could not be customized.

In Office 2010, you can customize the Ribbon to add new buttons, new tabs or new groups.  You can also rename the existing tabs and groups to suit your needs.

To add a new tab or group to the ribbon:

1. Click the File tab.
2. Click Options to open the Word Options screen.
3. Select the Customize Ribbon category on the left.

Use the controls in this screen to add commands to the ribbon, or to create new tabs or new groups.

On some charts, you may want to plot a data series that contains different numbers on a different scale.  For example, you may want to show monthly sales figures and percentages on the same chart.  If you use the same scale for both data series, the percentages will be too small to show up on the chart.

To plot a data series on a secondary axis:

1. Create your chart.
2. Select the smaller data series by clicking the drop-down list on the Layout tab of the Chart Tools ribbon.
3. Click the Format Selection button.
4. Excel will display the Format Data Series dialog box
5. In the Series Options category, select Secondary Axis.
6. Click the Close button.
7. Excel will plot the data series on a secondary axis.

If you are working with a formula that may refer to a blank cell or otherwise generate an error message, you can use a combination of the IF-THEN-ELSE and the ISERROR function to handle the #DIV/0! or other error messages that may appear.

The IF-THEN-ELSE has 3 parts: the condition, the TRUE condition, the FALSE condition. The following formula tests A1 for the number 100. If it is found, the cell displays the contents of A1. If it is not found, it multiplies another cell (B1) by 2:

=IF(A1=100,A1,B1*2)

By using one of the ISERROR functions, you can test for an error condition and use the IF statement to display a message or nothing at all. The common IS functions are ISERR, which returns TRUE for any error value except #N/A and ISERROR, which returns TRUE for any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

Suppose a cell is going to contain the average of several cells. If the cells are empty, the result will be division by zero and the #DIV/0! error will appear. The following formula will take care of this condition:

=IF(ISERR(AVERAGE(A1:A10)),”No Data”,AVERAGE(A1:A10))

If the formula AVERAGE(A1:A10) returns an error (most likely because there are no numbers in the range), the cell will display the words “No Data”. If there is no error, the cell will display the results of AVERAGE(A1:A10).

Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.

Extending a Cell’s Contents

The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:

• Text will be copied
• A single number will be repeated.
• A range of numbers will create a series based on the differences between the numbers
• A formula will be copied and all cell references will change appropriately.
• A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
• A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)

Smart-Tag Options

With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:

• Text or formula will let you extend with or without formatting, or just the formatting.
• Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
• Dates will let you fill days, weekdays, months, years, with or without formatting.

Right-Dragging the Extend Box

If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.

Double-Clicking Magic

Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.