Popular Courses
Popular Courses

# Tips & Tricks

Converting Numeric Values to Times

We have a lot of worksheets that contain times. The problem is that the times are in the format "1300" instead of the format "13:00." Thus, Excel sees them as regular numeric values instead of recognizing them as times. Sam wants them to be converted to actual time values.

There are several ways you can approach this task. One way is to use the TIME function to convert the value to a time, as shown here:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

This formula assumes that the time in cell A1 will always contain four digits. If it does not (for instance, it might be 427 instead of 0427), then the formula needs to be modified slightly:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

The formula basically pulls the leftmost digit (or digits) and uses them for the hours argument of the TIME function, and then uses the two rightmost digits for the minutes argument. TIME returns an actual time value, formatted as such in the cell.

A similar formulaic approach can be taken using the TIMEVALUE function:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

This formula uses REPLACE to insert a colon in the proper place, and then TIMEVALUE converts the result into a time value. You will need to format the resulting cell so that it displays the time as you want.

Another variation on the formulaic approach is to use the TEXT function, in this manner:

=TEXT(A1,"00\:00")

This returns an actual time value, which you will then need to format properly to be displayed as a time.

Another approach is to simply do the math on the original time to convert it to a time value used by Excel. This is easy once you realize that time values are nothing more than a fractional part of a day. Thus, a time value is a number between 0 and 1, derived by dividing the hours by 24 (the hours in a day) and the minutes by 1440 (the minutes in a day). Here is a formula that does that:

=INT(A1/100)/24+MOD(A1,100)/1440

This determines the hour portion of the original value, which is then divided by 24. The minute portion (the part left over from the original value) is then divided by 1440 and added to the first part. You can then format the result as a time, and it works perfectly.

All of the formulas described so far utilize a new column in order to do the conversions. This is handy, but you may want to actually convert the value in-place, without the need for a formula. This is where a macro can come in handy. The following macro will convert whatever cells you have selected into time values and format the cells appropriately:

Sub NumberToTime()

Dim rCell As Range

Dim iHours As Integer

Dim iMins As Integer

For Each rCell In Selection

If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then

iHours = rCell.Value \ 100

iMins = rCell.Value Mod 100

rCell.Value = (iHours + iMins / 60) / 24

rCell.NumberFormat = "h:mm AM/PM"

End If

Next

End Sub

The macro uses an integer division to determine the number of hours (iHours) and stuffs the remainder into iMins. This is then adjusted into a time value and placed back into the cell, which is then formatted as a time. You can change the cell format, if desired, to any of the other time formats supported by Excel.

End-of-Month Calculations

There are many ways you can use Excel to calculate the date at the end of the next month. One such way, using the EOMONTH function. There are ways you can do it, however, without using that particular function. (Some may not want to use it because the EOMONTH function used to only be available if the Analysis Toolpak was loaded. If you couldn't count on it being loaded, it doesn't make sense to rely on the function.)

For instance, one approach is to AutoFill for the last days. Let's say you wanted the last days of a series of months in the first column, beginning at A4. All you need to do is this:

1. In cell A4, enter the last day of the current month, such as 30 Apr 2017.
2. In cell A5, enter the last day of next month, such as 31 May 2017.
3. Select both cells, A4 and A5.
4. Click on the small square handle at the bottom right corner of the selection.
5. Drag the mouse downward as many cells as desired.

The result is that the area you drag over in step 5 is filled with end-of-month dates for the next however many months. Pretty cool! A slight variation on these steps could also be used:

1. In cell A4, enter the last day of the current month, such as 30 Apr 2017.
2. Select cell A4.
3. Right-click on the small square handle at the bottom right corner of the selection.
4. Drag the mouse downward as many cells as desired. When you release the mouse button, Excel displays a Context menu.
5. From the Context menu, choose Fill Months.

If you are not an AutoFill type of person, and instead prefer to use formulas, you could enter the starting end-of-month date in cell A4 (it must be an actual end-of-month date) and then the following formula in A5:

=DATE(YEAR(A4),MONTH(A4)+2,1)-1

This formula calculates the date for the first day of the month two months in the future, and then subtracts one from it. The result is the last day of the next month. The formula wraps around the end of years just fine, since the DATE function increments the years properly if the month value provided is greater than 12.

Another formulaic approach is to use the following:

=A4+32-DAY(A4+32)

This formula works because it adds 32 to the starting date (to make sure you are past the end of the following month), and then subtracts the number of days the result is past the end of the month.

Making Pane Settings Persist

If we freeze panes in a worksheet and then save the workbook, the next time we open that workbook the previously frozen panes no longer appear. Each time we open the workbook, we need to reset the panes. We don't think it used to be this way in older versions of Excel and wonder if there is some setting we need to make or wonder, perhaps, if Excel has changed how it handles panes. We want to save the pane settings with the workbook so they persist from one usage to another.

The default behavior of the latest versions of Excel is that your pane settings should be persistent, just as we remember in older versions of Excel. If that is apparently not happening for you, there are a few things you can check:

• See if someone else is updating or using the workbook and, while doing so, removing the panes.
• Check to see if the workbook has a macro that runs automatically when starting that removes the panes. You might try looking for the text "Freeze Panes" in the macros.)
• See if the workbook is actually being saved in a non-Excel format, such as CSV or HTML. Other formats don't necessarily hold on to some settings, such as panes. (Save the file in XLS or XLSM format to see if that fixes the problem.)
• Is the workbook, when open, being worked with using multiple windows? If so, and one of the windows doesn't use panes, the settings in the last-closed window are those that will "stick" in the workbook.
• Check if the workbook is being shared with others. Some users report an oddity where pane settings may not save properly in shared workbooks.
• Are filters being used in the workbook? If you apply filters, then set panes, and finally remove filters, the panes may also go away.

If none of those ring a bell with you, try starting with a brand new, blank workbook. Put some test data in it, freeze the panes, and then save it. Exit Excel and open the workbook again. If the panes are still there, then this is a good sign that the problem is with the other workbook only. In that case, it could be that the workbook is becoming corrupted (for some reason) and you may need to work on getting your data into a different workbook.

There are two other things you can do, if you desire. One is to simply save a custom view of your worksheet, with the panes in place. You should then be able to load the custom view at a later time and have the pane settings be present (along with many other settings) so that you can continue working with the workbook.

The other thing you could try is to create your own macro that sets the panes as you want them to appear. Here's an example:

Private Sub Workbook_Open()

Sheets("Sheet1").Range("D4").Select

ActiveWindow.FreezePanes = True

End Sub

This macro would be added to the Workbook module, and you'll need to change the cell reference (D4) and worksheet name (Sheet1) to reflect where you want the panes set. You could also, if desired, change the code to a "regular" macro that could be assigned to a shortcut key or the Quick Access Toolbar. That way you could use the macro to set similar panes in any worksheet, with the click of a button.

Sub SetPanes()

ActiveSheet.Range("D4").Select

ActiveWindow.FreezePanes = True

End Sub

Using Stored Views

Once you have defined the views for a worksheet, you can use them to look at your information in different ways quickly. To select different views, follow these steps:

1. Display the View tab of the ribbon.

2. Click the Custom Views tool in the Workbook Views group. Excel displays the Custom Views dialog box. (See Figure 1.)

Figure 1. The Custom Views dialog box.

3. Select a view from those listed in the dialog box.

4. Click on the Show button. Your display settings are changed to reflect what was previously saved in the view.

# Formatting Currency

There is a way to easily format cells so what would normally appear as \$10,000.00 would appear as \$10.000,00. This format being described is the difference between the US method of displaying figures (using commas as thousand separators and a period as a decimal sign) and the European method of displaying figures (using periods as thousand separators and a comma as a decimal sign).

There are three ways you can accomplish a switch. The easiest method is to simply change the Regional Settings in Windows. The exact way you do this depends on the version of Windows you are using, but in general there is a choice in the Windows Control Panel that allows you to specify regional settings. All you need to do is modify those settings to match the numeric display format desired. The change will affect not only the display of numbers in Excel, but in other Windows-compliant programs, as well.

The second method is to use a formula to handle the numeric display. This has the drawback of converting the numeric value to text, but it could be easily done. For instance, let's assume that you have the formatted numeric value \$10,000.00 in cell A1. The following formula, in a different cell, would display the text \$10.000,00:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,

"\$#,##0.00"),".","^"),",","."),"^",",")

This formula first converts the number to an initial currency format in text. Then the SUBSTITUTE function is used to first change "." to "^" ("^" is used as a temporary placeholder), and then change "," to ".", and finally "^" to ",".

The final method has the advantage of leaving your numbers as numbers, instead relying on a custom format. All you need to do is to multiply your values by 100 and then use the following custom format:

#"."###"."###","##

The format allows any number up to 9.999.999,00 to be used. If you deal with numbers that have more than two decimal places, you will need to adjust your custom format accordingly, or adjust the value being displayed so that it has nothing to the right of the decimal point after it is multiplied by 100.

Summing Based on Part of the Information in a Cell

We have a worksheet that includes information for all the parts in our warehouse. In this sheet, part numbers are shown in column A using the format 12345 XXX, where XXX represents a location code. This means we could have multiple entries on the worksheet for the same part numbers, but each entry representing a different location for that part. We need a formula that sums the values associated with each part number, regardless of its location code. Thus, we need a way to sum the quantity column related to parts 12345 ABC, 12345 DEF, 12345 GHI, etc. We need a way to do this without splitting the location code to a different column.

There is more than one way to get the desired answer. For the sake of the examples in this tip, assume that the part numbers are in column A and that the quantities for each part are in column B. It is these quantities that need to be summed, based upon just a portion of what is in each cell in column A. Further, you can put the part number (minus the location code) desired in cell D2.

The first potential solution is to use the SUMPRODUCT function, in this manner:

=SUMPRODUCT((VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

This formula checks the values in the range A2:A49. You should make sure that this range reflects the range of your actual data. If you generalize the formula so that it looks at all of columns A and B (as in A:A and B:B), you'll get a #VALUE error, since it tries to apply the formula to empty cells in the columns.

You can get a similar result by using an array formula such as this:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Remember, again, that this is an array formula, so you need to enter it by pressing Shift+Ctrl+Enter. Note, as well, that this formula converts the value in D2 to text for the comparison. This wasn't done in the previous formula because there the substring picked out of column A was converted to a numeric value using the VALUE function.

You can also use the DSUM function to construct a working formula. Let's assume that the part numbers (column A) have a column header in cell A1. Copy this column header (such as "Part Num") to another cell in the worksheet, such as cell D1. In cell D2, enter the part number, without its location code, followed by an asterisk. For example, you could enter "12345*" (without the quote marks) into cell D2. With that specification set up, you can then use this formula:

=DSUM(\$A\$1:\$B\$49,\$B\$1,D1:D2)

This formula uses the specification in cell D2 (the characters 12345 followed by anything) as a key to which values from column B should be summed.

Finally, if you had the same specification in cell D2 as you used with the DSUM approach, you could use a very simple SUMIF function, in this manner:

=SUMIF(A:A,D2,B:B)

Note that this approach allows you to use the full column ranges (A:A and B:B) in the formula.

If your part numbers (in column A) are not as consistent in their format as you might like, then you may be better creating a user-defined function to find your quantities. For instance, if your part numbers aren't always the same length or if the part numbers can contain both digits and letters or dashes, then a UDF is the way to go. The following example works great; it keys on the presence of at least one space in the value. (Kathy indicated that a space separated the part number from the location code.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _

FindPart As Variant) As Long

Dim Pos As Integer

Dim Pos2 As Integer

Dim i As Long

Dim tmp As String

Dim tmpSum As Long

Dim PC As Long

PC = Parts.Count

If PartsQty.Count <> PC Then

MsgBox "Parts and PartsQty must be the same length", vbCritical

Exit Function

End If

For i = 1 To PC

Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")

If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then

tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then

tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then

tmpSum = tmpSum + PartStock(i)

End If

Next i

End Function

To use the function, in your worksheet call it using two ranges and the part number you want:

Pop-Up Comments for Graphics

We know how to add comments to cells so that when you hover the mouse over the cell you can see the comment. We would like to do the same thing with graphics - have a comment or pop-up box appear when a person hovers the mouse over a graphic placed in a worksheet. While we could adjust cell size to match the graphic and then attach the comment to the cell, the size of the graphics we are using really don't make that practical. We wonder if there is a way to have pop-up comments appear when someone moves the mouse over a graphic in a worksheet.

There is no way to do this using the Comments feature of Excel, but there are some workarounds. The first involves using hyperlinks. Just follow these steps:

1. Insert the graphic in your worksheet and size as desired.

2. Select the graphic (click on it once).

3. Press Ctrl+K. Excel displays the Insert Hyperlink dialog box. (See Figure 1.)

Figure 1. The Insert Hyperlink dialog box.

4. Click the Place In This Document button.

5 If desired, in the Type the Cell Reference box, enter the address of a cell close to or behind your graphic.

6. Click the ScreenTip button. Excel displays the Set Hyperlink ScreenTip dialog box.

7. Enter the text you want displayed.

8. Click on OK to dismiss the Hyperlink ScreenTip dialog box.

9. Click on the OK button to dismiss the Insert Hyperlink dialog box.

The result is that when someone hovers the mouse pointer over the graphic, a small note appears - usually below the graphic - that contains the ScreenTip text. It isn't quite as noticeable as a regular Excel Comment, but it does provide a little assistance.

If you want something a bit harder to miss, then a macro might be helpful. There are a number of different ways you could approach a macro-based solution, but perhaps the easiest is to simply create a macro such as the following:

Sub MyMacro()

MsgBox "This is my comment"

End Sub

Back in your worksheet, right-click on the graphic and choose Assign Macro from the resulting Context menu. Excel shows you a list of all the macros available to you; you should pick the short one you just created (in the example above it is "MyMacro").

Now, when you click on the graphic, you see a message box that contains whatever text you specified in your macro. It isn't quite as automatic as only requiring the person to scroll over the graphic, but it does provide a handy way to convey a lot of information to the user.

Setting Up Custom Auto Filtering

When we are using Excel's Auto Filtering feature, we may want to display information in our list according to a custom set of criteria.

Excel makes this easy to do. All we need to do is the following:

1. If Auto Filtering is not already turned on, display the Data tab of the ribbon and click the Filter tool.

2. Use the drop-down arrow to the right of a column label to select Number Filters | Custom Filter or Text Filters | Custom Filter. (The names of the options, and thus the choices you make, depend on the composition of your data.) Excel displays the Custom AutoFilter dialog box. (See Figure 1.)

Figure 1. The Custom AutoFilter dialog box.

3. Use the controls in the dialog box to set the criteria you want used for filtering your list.

4. Click on OK.

We can use the Custom AutoFilter dialog box to set any combination of criteria that we need. For instance, we can indicate that we want to see any values below, within, or above any given thresholds we desire. The filtering criteria will even work just fine with text values. For instance, we can cause Excel to display only records that are greater than AE. This means that anything beginning with AA through AE won't be displayed in the filtered list.

We should note that Excel also provides wildcard characters we can use to filter text values. These are the same wildcards we can use in specifying file names at the Windows command prompt. For instance, the question mark matches any single character, and the asterisk matches any number of characters. Thus, if we wanted to only display records that have the letter T in the third character position, we would use the equal sign operator (=) and a value of ??T*. This means the first two characters can be anything, the third character must be a T, and the rest can be anything.

# Picking a Group of Cells

Excel allows you to define a group of cells in preparation for doing an action, such as formatting the cells. This is different than picking a range of cells, however. A range of cells is contiguous in nature - every cell between a starting and ending point is selected. A group of cells does not need to be contiguous. Instead, they can be anywhere on the worksheet.

In some Microsoft documentation, a group of cells is called a selection set. To put together your own group of cells, you need to use the mouse. Click on the first cell in the group. As you click on each subsequent cell in the group, simply hold down the Ctrl key. Each cell you click on is added to the group. If you click on a cell a second time (with the Ctrl key pressed), the cell is removed from the group. If you click on any cell without holding down the Ctrl key, that cell is selected and the selection set is gone.

# Deleting Rows before a Cutoff Date

We have a large worksheet containing several thousand rows of data. Column B contains a date, and we need to delete all the rows in which the date in column B is earlier than a specific cut-off date. We wonder about the easiest way to do this for so much data.

This is rather easy to do, with the approach you use dependent on how often you need to do it and how you want to work with your data. If you don't care what order your data is in, then the easiest method is what I refer to as the "sort and delete" method:

1. Select cell B2. (This assumes that B2 is the first date in your rows of data because row 1 contains headers.)
2. Display the Data tab of the ribbon.
3. Click the Sort Oldest to Newest tool. Excel sorts the data according to the dates in column B, with the oldest date in row 2.
4. Select and delete the rows that contain dates before your cut-off.

This works great if you only need to perform that task once in a while and if you don't mind the rows in the data being reordered. If reordering is a problem, then you may want to add a column to your data and fill that column with values from 1 to however many rows of data you have. You can then perform the "sort and delete" method, but afterwards resort your data based on the values in the column you added.

Of course, you could also use a "filter and delete" method, which will leave your data in its original order without the need of a helper column:

1. Select cell B2. (This assumes that B2 is the first date in your rows of data because row 1 contains headers.)
2. Press Ctrl+Shift+L. Excel applies AutoFilter to your data. (You should be able to see the small drop-down arrows next to the headers in row 1.)
3. Click the drop-down arrow next to the Date header in cell B1. Excel displays some sorting and filtering options.
4. Hover your mouse pointer over the Date Filters option. Excel displays even more options.
5. Choose the Before option. Excel displays the Custom AutoFilter dialog box.
6. In the box to the right of "Is Before," specify a date one day after your cut-off date.
7. Click OK. Excel applies the filter and you can only see those rows that are at or before your cut-off date.
8. Select all the rows, but not row 1. (That's because row 1 contains your headers.)
9. Display the Home tab of the ribbon.
10. Click the Delete tool. Excel deletes all the selected rows.
11. Display the Data tab of the ribbon.
12. Click the Filter tool to remove the AutoFilter.

If you need to perform the task of removing rows often, then you won't be able to beat the convenience of using a macro. The following macro assumes that you've placed the cut-off date into cell K1. It grabs this date and then looks at each row in your data, deleting any rows that are before this cut-off date.

Sub DeleteRowsBeforeCutoff()

Dim LastRow As Integer

Dim J As Integer

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

For J = LastRow To 1 Step -1

If Cells(J, 2) < [K1] Then

Cells(J, 2).EntireRow.Delete

End If

Next J

Application.ScreenUpdating = True

End Sub

Correlation Analysis

Web data analysis comes with its own set of data inconsistencies and irregularities that cannot be explained by simple math. Sometimes you may notice that your website bounce rate changes with the day of the week, and sometimes the conversion rate will change with traffic. This happens when one data set (visits) shares positive or negative relationship with another data set (conversion).

Pearson`s correlation is the best way to measure the positive and the negative correlation of data to rule out the data inconsistencies and establish a baseline.

MS Excel 2007 is equipped with Correlation function and can be used to perform a quick analysis.

Let`s assume that we want to see the relationship between the daily visits and the website conversion.

a. Enter the visit and the conversion data on the Excel spreadsheet.

b. Place the cursor on any empty cell in the spreadsheet. This cell will be used to display the Pearson`s correlation coefficient. Press F2 to enter the formula for correlation and type "=Correl" and hit Tab key.

c. Once you hit the Tab key, the cursor will be placed in the bracket, and you will be allowed to select the array data. Select your first data column (avg. daily visits) for the array1 and second data column (avg. daily conversion) for the array2. Close the bracket and then hit the Enter key.

d. The value displayed in the cell (0.963240336) will be the correlation between the avg. daily visits and avg. daily conversion. In this case, the daily visits show a high positive correlation with conversion (greater than zero is positive and less than zero is negative. Zero is no correlation).

Reference Shortcut

In past issues of ExcelTips you learned the differences between absolute and relative cell references. One of the shortcuts provided by Excel allows you to quickly cycle through the various forms of reference for a cell. All you need to do is position the insertion point in your formula somewhere within a reference you have entered. For instance, if you entered the cell reference B1, simply make sure the insertion point is before the B, after the B, or after the 1. You can then press the F4 key to start cycling.

Each time you press F4, Excel adds different permutations of the dollar sign (\$). The first time you press, the reference becomes \$B\$1, the second time it is B\$1, the third time it is \$B1, and the fourth it is back to plain old B1.

# Incrementing Numeric Portions of Serial Numbers

If you have a range of serial numbers in the format A12345678B and you would like to find a formula that will increment the numeric portion of the serial numbers by 1. Thus, the next number in sequence would be A12345679B, then A12345680B.

There are actually a couple of ways you can go about this, and the first doesn't really involve a formula at all. Instead you can create a custom format that displays your serial number; the format should look like this:

"A"#"B"

Then, in a cell that has this format applied, you only need to include the numeric portion of the serial number (12345678). You can then use regular AutoFill techniques to fill out as many cells as necessary with the serial number.

If you really want to use a formula, then the following should work just fine as long as the pattern for the serial number is a single letter, eight numeric digits, and a single terminating letter:

=LEFT(A1,1) & MID(A1,2,8)+1 & RIGHT(A1,1)

This assumes that cell A1 contains the beginning serial number. If you put the formula in cell A2, it could be copied down as many times as necessary for the desired number of serial numbers.

If the numeric portion of the serial number could start with leading zeroes, then you need to use a different formula to provide the proper zero padding:

=LEFT(A1,1) & TEXT(VALUE(MID(A1,2,8))+1,"00000000") & RIGHT(A1,1)

Excel - Limiting Input by Time of Day

There are two general ways you can go about this. One approach is to use Data Validation to check the time and either allow or disallow data entry.

1. Select all the cells in the worksheet.
2. Display the Data tab of the ribbon.
3. Click the Data Validation tool in the Data Tools group. Excel displays the Data Validation dialog box. (See Figure 1.)
4. Using the Allow drop-down list, choose Custom.
5. Enter the following in the Formula box:

=OR(24*MOD(NOW(),1)18.5)

1. Make changes on the Error Alert tab, as desired.
2. Click OK.

The problem with this approach is in the very first step: You need to select all the cells in the worksheet in order to prevent data being entered in any of them. Plus, if you already are using Data Validation in any of the cells, this approach will overwrite those settings.

For these reasons, it may be better to use a macro-based approach. All such approaches can utilize event handlers to check for any changes. The following relies on the Worksheet_Change event, which means it is triggered only when Excel detects a change in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sMsg As String

sMsg = "No entries allowed between 4:00 pm and 6:30 pm!"

If Time >= "4:00:00 PM" And Time

MsgBox sMsg, vbCritical

With Application

.EnableEvents = False

.Undo   ' This undoes the change the person made

.EnableEvents = True

End With

End If

End Sub

Essentially, every time there is a change in the worksheet, the handler checks to see if it is between 4:00 pm and 6:30 pm. If it is, then a message box is displayed to indicate the error, and then the .Undo method is used to roll back any change that was attempted.

If you prefer, you could take a different approach and protect the worksheet if it is within the banned time:

Private Sub Worksheet_Activate()

If Time >= "4:00:00 PM" And Time

ActiveSheet.Protect

MsgBox "Worksheet is protected."

Else

ActiveSheet.Unprotect

MsgBox "You are free to edit now."

End If

End Sub

The Worksheet_Activate event handler is invoked every time the worksheet is activated (selected). If the worksheet is activated anytime outside of the banned time, then it is unprotected. Of course, the user could still manually unprotect the worksheet even during the banned time, so it is a good idea to use this approach in conjunction with an approach that is triggered every time a change is attempted, as discuss Excel includes several different form controls that you can add to your worksheets. One of ted earlier.

# Excel - Adding and Using a Combo Box

Excel includes several different form controls that you can add to your worksheets. One of these controls is a combo box. This control allows you to pick an option from a drop-down list, and then determine what was picked. To create a combo box, follow these steps:

Somewhere in your worksheet, create a list that specifies what you want to appear in the combo box. For instance, if you have a list of names you want to appear in the combo box, create that list of names in your worksheet. (For this example, let's assume that you create the list in cells K7 through K13.)

1. Make sure the Forms toolbar is displayed. (Choose View | Toolbars | Forms.)
2. Click on the Combo Box tool in the toolbar. The mouse pointer changes to a small crosshair.
3. Create the actual combo box by clicking and dragging to define the parameters of the control. When you release the mouse button, the combo box appears in your worksheet.
4. Right-click on the newly created combo box. A Context menu appears.
5. Choose the Format Control option from the Context menu. Excel displays the Control tab of the Format Control dialog box.(See Figure 1.)
6. In the Input Range box, specify the range used by the list you created in step 1. (For instance, K7:K13.) You can also click once in the Input Range box and then use the mouse to select the range in the worksheet.
7. In the Cell Link box, specify the worksheet cell that you want to contain the index value of what is selected in the combo box.
8. Click on OK.

Your combo box should now work properly. If you click on the down-arrow to the right of the combo box, you should see the items from you list. If someone selects an option in the combo box, the cell you specified in step 7 is updated to contain the relative position of the item selected in the combo box. In other words, if some selects the fourth item in the combo box, then the cell specified in step 7 will contain the value 4. (Similarly, if you change the value at that cell-the one specified in step 7-to a different value, then Excel changes what is displayed in the combo box.)

# Photoshop Magic Wand Tool

The Magic Wand is another selection tool, ideal for when you are working with a background that is very monotone and consistent. If you have a clearly defined color that you want to choose in an image, this is the tool for you. For example, the Magic Wand is great when you want to select a white background or a clear blue sky.

Choose the magic wand tool from the tools panel and click on the part of the image you want to select. Make sure that you toggled the ''add to selection'' option on the top bar (icon of two squares) so you can keep on adding colors and tones to your selection.

# Excel Function Keys and Shortcuts

Function Keys in Excel are a handy and faster way of doing certain tasks by using keyboard instead of mouse. Function keys provide same output in all versions of Excel making it easier to recall.

F1 Key:

''F1'' is used for opening ''Excel Help''. Its output is same as obtained by clicking on ''question mark button'' available on top right hand side of your excel sheets as highlighted in below image.

Alt + F1:

If you use ''Alt and F1'' Keys together then it will insert a new chart in your excel and will open the chart options. It is a column chart by default as shown in below image.

Ctrl + F1:

You can minimize or maximize the ribbon of excel by pressing ''Ctrl & F1'' Keys together.  By minimizing the ribbon only tab names will be displayed on the ribbon. This could also be achieved by clicking on the button highlighted in below image:

# Word - How to Save Images from MS Word Document

• If you want to save one or few pictures from an MS Word document, you can take right click on the image and select ''Save as Picture...'' option. This feature has been made available in the recent versions of MS Word. But what if you have 200 images in the document?
• The quickest and easiest method of saving all the images from a Word document is to save the document as a webpage. If you know HTML programming, you would understand that a webpage refers to resources like images stored as individual files.

So, when we save a Word document as webpage, MS Word document becomes an HTML page and all the embedded images get stored in a separate folder. We can use this operation to get images out of the Word document.

1. Open the MS Word document containing images
2. Go to File ans then select Save As
3. Save As dialog box will appear
4. Select Web Page from Save as type list
5. Click Save button to save the document as a webpage
6. Open Windows Explorer and browse to the location where you have saved the document as webpage
7. In this location, you will find a newly created folder with the same name as that of the MS Word document. This folder will contain all the images that were there in the document. There might be some other files as well, but you can ignore them
8. You have got the images as files. Now you can use or edit them separately

Estimate time needed and actual time used

The application's Gantt chart has a bar for each task representing the time at which the task will be done and how long it will take. If you grab the left edge of a bar and drag, you can indicate how far along you are. If you run into problems (gee, that never happens) and the task is going to take longer,you can grab the right edge and extend the time needed (see the picture below).

Mullan Training will be providing regular Tips and Tricks to help enhance your use of Microsoft Excel, Word and other applications.

Keep up to date with these tips by liking our Facebook page!

Play Music in the Background During PowerPoint  Presentations

Here's a fun tip: punch up your PowerPoint presentation with some tunes. While playing music in the background certainly isn't always appropriate, adding audio for the duration of your presentation is an easy process that can make your slides a bit more interesting.

First, you'll want to ensure that you have your music or audio file saved on your computer (or an accessible cloud location) and that you have the rights to play the music in the setting where you're presenting. See this page for audio formats that PowerPoint can use. Then:

1. From the Insert tab, select the Audio button.
2. Locate the music file you wish to include and click Insert.
3. From the Playback tab, click Play in Background.

If your presentation will be longer than the song you chose, you can add more than one song. Simply repeat steps 1-3 on any slide on which you want to add a new or additional track.

Be the Boss of Your Slides by Using the Slide  Master in PowerPoint

You've finally finished the first slide in your PowerPoint presentation. The formatting, colors, and transitions are, in a word, perfection... and now you only have 27 slides to go.

Never fear! With PowerPoint 2013/2016, there's no need to waste time duplicating your formatting for each additional slide in your presentation. Once you get a slide exactly the way you want it, the Slide Master allows you to consistently format every slide, all at once, all in one place, and with one simple step. You'll be able to spend more time focusing on the content of your presentation, rather than its formatting.

To quickly format all of the slides in a presentation in PowerPoint 2013/2016:

1. Open the View tab and select Slide Master.
2. In the Slide Master View, the Master Slide and all the slide layouts in the theme are shown.
3. Click on the Master Slide (or the slide you want to adjust) and edit it how you want.
4. The changes will translate to each of the slides in the theme.
5. You can see how your changes impact each slide layout so you can edit your design till you get it right.
6. When you go back to Normal View, your changes will be saved.

How to Import/Migrate Gmail Contacts to Outlook

If you use Outlook 2013 or the Outlook Web App, it's easy to import contacts from other email services. If you've moved from Google Apps to Office 365, from Google Apps for Work to Office 365 for Business, or you're just using both email clients and want to have the same contacts in both, you can follow the instructions below.

## To export Gmail contacts:

1. From your Gmail account, click Gmail -> Contacts.
2. Click More >.
3. Click Export.
4. Select the contact group you wish to export.
5. Select the export format Outlook CSV format (for importing into Outlook or another application).
6. Click Export.
7. When prompted, click Save as, and browse to a location to save the file.

## To import from Outlook 2013:

1. From the FILEtab, select Open & Export.
2. Select Import/Export.
3. In the Import and Export Wizard, select Import from another program or file.
4. Click Next.
5. Select Comma Separated Values.
6. Click Next.
7. In the Import a File box, browse to and select the .csv file you saved your Gmail contacts to.
8. Select Replace duplicates with items imported, Allow duplicates to be created, or Do not import duplicate items.
9. Click Next.
10. In the folder list, select the contacts folder where you want to import your contacts to, and click Next.
11. Click Finish.

## To import from Outlook Web App:

1. Select People from the app launcher.
2. Select the settings gear, them click Options.
3. Select Import contacts under People.
4. Click Browse and select the .csv file you saved your Gmail contacts to.
5. Click Import.

For instructions on migrating contacts from Yahoo!, Outlook.com, Mozilla Thunderbird, or a desktop version of Outlook, please see this page.

These 4 Keyboard Shortcuts in PowerPoint Will Make Presentations a Breeze

Beginning to build a presentation PowerPoint can be quite a journey and an undertaking. To make it easier along the way, use these four shortcuts to move swiftly while you create your masterpiece.

## Increase or Decrease Font Size

To quickly increase or decrease your font in PowerPoint, select the font you want to adjust and hit CTRL + Shift + < or >.

## Make it Simple

When you are playing around with the text formatting, this keyboard is great to clear your slate. Press CTRL and the spacebar to remove all formatting.

## Create a New Slide Within Your Presentation

Use CTRL + M to create a new slide. Don't get confused with CTRL + N, this will open up a new PowerPoint presentation.

Tab Around All Objects on Your Slide

Objects can add up in PowerPoint. To quickly move between them instead of carefully selecting the one you want and having to click over and over again, use Shift+Tab to move through them.

How to Customize the Quick Access Toolbar in Office 2013

Today we're going to look at an often-overlooked secret weapon for productivity in Office 2013: the Quick Access Toolbar. Customizing this toolbar is one of the best ways to save time by creating shortcuts to your frequently-used commands in each Office product.

## What is the Quick Access Toolbar?

You'll see the Quick Access Toolbar above the Ribbon (by default) on Office 2013 programs like Excel, Word, PowerPoint, and Outlook. It's the home for one-click icons for your favorite or most often used commands and actions.

For example, if you frequently freeze panes in Excel, you can add that command to the Quick Access Toolbar so you can access it with a single click, rather than navigating to the View tab first.

## How do you customize the Quick Access Toolbar?

If you want to add a command from the Ribbon, all you have to do is right-click and select Add to Quick Access Toolbar.

If you want to add a command that you can't right-click, open the drop-down menu in the toolbar and select More Commands. From there, you can choose an action from the Popular Commands section, or select and choose from another option, like All Commands, in the drop-down menu.

Once you have your Quick Access Toolbar customized to your liking, you can get used to using those icons to take actions rather than navigating through the Ribbon. You also may wish to hide the Ribbon to save space and simplify your screen.

Easily Embed an Excel Spreadsheet in OneNote

OneNote allows you to kinds of information into notebooks, including other Office files. Adding an Excel spreadsheet to your OneNote page is a great way to create a copy of your Excel data to view in OneNote, so you don't have to switch back and forth between applications.

There are a few different ways to embed your spreadsheet. Begin from the Insert tab in OneNote:

1. To insert a blank spreadsheet, select Table -> New Excel Spreadsheet or Spreadsheet -> New Excel Spreadsheet
2. To insert an existing spreadsheet, select Spreadsheet -> Existing Excel Spreadsheet.

Note This process is best done when you don't have further changes to make to your Excel spreadsheet. Changes made in OneNote won't appear in the original Excel file, and vice versa-changes made in the Excel file won't appear on the OneNote page.

Enhance Your Images with Text in Word

Using layers in your Word document can take you a long way when it comes to designing an event invitation or professional looking document. Here's a quick tip on how to add text on top of your images in Word.

• Insert an image you'd like to use.
• Select the image.
• An icon will appear in the top right called Layout Options. Select Behind Text.
• You can now type on top of your text and also move around the image.

11 Features to Get the Most Out of Your Business Email

In the world of work, email is a mainstay. It allows us all to communicate at lightning-fast speeds-without having to pick up the phone. It gives us the power to thoughtfully consider projects, questions, answers, opinions, requests and feedback, all of which opens the door to better communication. And it gives us the ability to see the progression of conversations without digging through a mountain of individual messages. However, most people aren't taking full advantage of what their email systems offer. Sure, they know how to send messages, set tasks, make notes, block their calendars and schedule reminders, but there are also some pretty great features (and hacks) that you may not know about that could just transform the way you use business email. Let's take a look:

1. Future delivery/delayed delivery: Whether you have news that you don't want to share immediately or a project that's ready for delivery but you don't want to submit it too early, future/delayed delivery allows you to create and distribute messages on your schedule.
2. Message grouping: For heavy email users, the ability to group messages into conversations related to specific topics can help you keep track of emails, stay up to date on developments and work more efficiently.
3. Conditional formatting: Like message grouping, conditional formatting can help you organize conversations and stay on top of a certain topic. However, instead of grouping all messages into a batch that you can browse at a glance, conditional formatting simply displays messages matching selected criteria in the font and/or color of your choice.
4. Rules: Providing you with yet another organizational option, the rules feature allows you to create and manage settings that empower your email system to automatically process messages in a certain way. Some systems can check for and include sender, recipient, email size, date and more. This way, you'll never miss a message from your boss or your most important client; messages with large attachments can be singled out, etc.
5. Email to text: For those times when you need an immediate response, or need to contact someone who may not have access to his or her email account, a text message may be the best way to get in touch. Some business email providers offer this feature, others don't. If yours does, it can help you relay urgent messages quickly. Just be careful to reserve use of this feature for those who prefer it or for truly critical situations-because it can be disruptive to receive a barrage of non-urgent text messages while in a meeting, on vacation, etc.
6. Desktop alerts: Whether you need to reduce distractions for a day or indefinitely, by disabling desktop alerts you can eliminate pop-up notifications about new messages and focus on whatever the task at hand may be. Conversely, if you're waiting for an important email that you can't afford to miss by a moment, you can easily turn on desktop alerts for a few hours or turn them on and leave them on for good.
7. Email templates: If you're a PR exec, public information officer, customer service representative, work in sales or are in any position that requires you to field requests for information about a program, service, product or topic, email templates can help you make quick work of your responses. After creating your template, all you'll have to do is locate the template, personalize it as necessary and hit send. Not only will it help you save time, but increase your productivity. Plus, by not having to repeat yourself over and again, it might just improve your job satisfaction.
8. Save messages as files: If you want to save certain emails in the same file as project work, contracts or other documents, you can simply drag your message from your email window to your file folder. Or, you can click SaveAs and choose to save a message in a specific location. This way, you can refer back to messages without digging through your inbox and/or archive.
9. Multi-action shortcuts: If your email system supports shortcuts, it may also support multi-action shortcuts, which gives you the ability to combine several shortcuts into one quick action. For instance, if you want to mark a group of emails as read and simultaneously move them to a specific folder, or even mark messages as read, flag them as important and forward them to your team, you can do that with multi-action shortcuts. Some email systems that support multi-action shortcuts also allow you to create your own shortcuts, so you can customize your actions and make quick work of managing your inbox.
10. Automated cleanup: Some email systems include an automated cleanup function that can help you save space and keep your inbox tidy. When a conversation (message and response) occurs over email, some email systems can analyze the contents of that conversation and determine if a message is completely contained within each thread. If it is, then the previous message will be automatically deleted-so you'll have access to both the original message and all comments within a single email conversation, but your inbox won't be clogged by redundant emails.
Of course, if someone keeps clogging up your inbox with messages you don't need or want, you can generally block their messages by using your "junk" or "spam" filters. But if you don't want to permanently prevent someone from contacting you via email, you can simply select to send certain conversations directly to your trash by clicking the Ignore button.
11. Delegate access: Finally, if you're going on sabbatical, an extended vacation or parental leave and you don't want to either check messages daily or weekly-or come back to a mountain of email-you may be able to give someone else permission to manage your inbox. Simply look at your account settings. If you see a Delegate Access button-or something similar-you can choose to give access to a co-worker who can step in and respond to messages in your absence. Of course, you'll still have the ability to check messages and respond as appropriate, but this feature can help you keep things in check while you're away.

How to Remove the Background from a Picture in PowerPoint

Technology offers a lot of tools to help you design and present visually attractive work. But you don't need to be a professional designer or have pricey, complicated software to give your work the style it deserves. PowerPoint has many built-in tools that make editing photos and images a breeze.

The Remove Background feature in PowerPoint 2016 helps you eliminate distracting backgrounds so the important parts of your image are front-and-center. In a few simple steps, you can create praiseworthy presentations and nobody will ever know you did all that fancy image-editing right within PowerPoint.

To remove the background from a picture in PowerPoint:

• Click on the image that has background you want to remove.
• On the Format tab, click Remove Background.
• PowerPoint will automatically select the part of the image to be kept.
• Adjust the selection to cover the area of the image that you want to keep.
• On the Background Removal Tool tab, click Mark Areas to Remove and select any other area that you want to remove.
• When you're finished, click Keep Changes.

How to Add Sound Effects to Animations in PowerPoint

The more interesting your PowerPoint slides are, the more engaged your audience will be. A great way to spice up your bullet points or images in a slide is to not only add animation, but to also add audio to the animation. Follow the steps below to make your PowerPoint presentation more interesting.

## Add the Animation

• Select the image you want to add animation to.
• Select Animation tab.
• Click the Add Animation button.
• From the menu, select More Motion Paths.
• A menu will appear where you can select from a variety of motion paths. Choose the one you want.
• Select OK.
• Your animation has now been added to the slide. You can adjust the animation from here if it needs further settings.

## Add Audio to the Animation

• From the Animation Pane, select the drop down menu that you can access from the arrow on the animation.
• Click Effect Options.
• A new menu will appear. Under Enhancements in the menu, you have the Sounds option to select from a list of pre-loaded audio snippets, or you can select "Other Sound..." from the list to import your own.
• Click OK once you've selected a sound.
• Now your audio is attached to your animation.

How to Create To Do Lists in OneNote

Creating checklists is a built-in capability of OneNote that you can use for all sorts of different things. Perhaps the most obvious use is a to do list, and in fact, the checkbox is actually called a To Do tag in OneNote.

You can use this tag throughout a OneNote page; it doesn't have to be used only in a line-by-line list format. For example, you may take meeting notes and use the To Do tag to flag and track specific action items.

How to add and use To Do tags to create checklists in OneNote (applies to OneNote 2013 and OneNote Online):

1. Click the line where you would like to add your checkbox.
2. From the Home tab, select To Do Tag under Tags OR press Ctrl + 1. A checkbox will appear next to the text.
3. Click within the box to add or remove the check mark.
4. Right-click on the box and select Remove Tag to remove the box.
5. Press Enter to automatically create a new line with a checkbox. If you don't want the checkbox on the next line, press enter again.

How to Delete an Unwanted Blank Page in Word

Microsoft Word users may run into this problem from time to time: a blank page either in the middle or at the end of a document that cannot seem to be deleted. Frustrating! You can choose to print only select pages, but that doesn't exactly solve the problem.

Here are some possible steps to delete your unwanted blank page.

## Basic solutions

• The most basic solution: go to your unwanted blank page, click as close to the bottom of the page as you can get, and press your backspace key until the page is removed.
• Go to the VIEW tab, select Navigation Pane in the Show section, select the blank page thumbnail in the left panel, and press your delete key until it is removed.
• Check your sections: Go to the PAGE LAYOUT tab, click the Margins button, and select Custom Margins. Click the Layout tab. In the drop-down menu next to Section start, make sure that New page is selected. Click OK.

## Use paragraph symbols

• Detect what's on the page by displaying paragraph marks and formatting symbols. Look on your blank page to see if there are any symbols, especially the paragraph mark. Select the symbol and delete it, and you will likely also delete your blank page.
• If your blank page is in the middle of the document, it may be due to a manual page break. With paragraph marks turned on, you will be able to see the page break. Select it and delete it.

## Trouble with tables

• If there is a table at the end of your document, Word will automatically insert a paragraph after it, often resulting in a blank page at the end. While you can't delete this inserted paragraph, you can make it extra small so it doesn't cause a new page to be displayed. With paragraph marks turned on, select the paragraph symbol and change the font size to 1 point.
• If you still see a paragraph mark on the blank page, change the spacing around it. Select the paragraph symbol, go to the Paragraph section and launch the Paragraph formatting dialog box by clicking the pop-out icon in the lower right corner of the section. On the Indents and Spacing tab, change any spacing before or after the paragraph to 0. Change the Line spacingto 0. Click OK.
• If those two options didn't work, you can hide the paragraph. Select the paragraph symbol, and launch the Font dialog box by clicking the pop-out icon in the lower-right corner of theFont section on the HOME tab. Select the Hidden check box in the Effects section and click OK.

How to Create a Contact Group/Distribution List in Outlook

Contact Groups are a great method for emailing a large group of recipients, like a department at your organization or even a family mailing list or other group like a team or club. You might be more familiar with the term "distribution list" - that's what Contact Groups were called in earlier versions of Office. Setting up a list like this in Outlook 2013 is easy.

To create a contact group/distribution list in Outlook 2013:

• From the Ribbon, select Address Book.
• In the dialog box that appears, select File, and then select New Entry.
• Choose New Contact Group and a new window will appear.
• To add recipients, click the Add Members button and choose your desired option. If you choose From Outlook Contacts a dialog box will appear and you can choose your members.
• Name the new group and click Save & Close.

Save Files Directly to OneDrive for Business from Outlook

A few weeks ago, the Office team announced new updates to OneDrive for Business. The new updates bring many new features that make it easier to save attachments to your OneDrive for Business. Users can now save directly to OneDrive to a dedicated folder for email attachments. If an email has multiple attachments, users also have the option to treat each attachment individually depending on where and how they want to save the attachment. These features make it easier to store your documents in one centralized location, and in the cloud.

To make saving files easier straight from your inbox, reference these steps below. Once you have an email open with an attachment or attachments:

• You have the option to save each attachment separately, or all at once.
• If saving files individually, choose Save to OneDrive from the drop down menu that appears when you hover over the arrow to the right of the document
• If you are saving all files to your OneDrive file, select Save all to OneDrive
• Once the documents have downloaded, you'll see confirmation that reads "Saved all to Email attachments"
• To open up the Email attachments folder, go to your OneDrive. Here you will find a folder with all of your downloaded documents from Outlook.

Frame Your Word Doc with A Border

When you're working on a piece of art, it's natural to want to frame it to help with the presentation. The same should go for your Word documents. Word is a great canvas for you to craft your documents and eventual PDFs, so why not put a frame on it?

In Word, frames are called borders and there are many ways to customize your border. Follow the steps below to learn more.

• Click the Design tab.
• Select Page Borders, the button on the far right side.
• A new window will appear with many options to customize your border and alter specific details.
• hen you've decided on the look of your border, click OK and your border will be applied.

How to Use Paste Options in Word

Copy and paste is a quintessential function in Office 365. Time is precious and ideally, if we can avoid retyping, then the more time we have. However, copying and pasting an Excel table into a Word doc can cause some roadblocks when it comes to formatting. Thankfully, there's a feature called Paste Options that provides several pasting options.

• When you have an Excel table ready to paste in a Word doc, find the Paste Options button in the top left of the window.
• Click the drop down menu to preview your Paste Options. You can hover over each option and a preview of the paste will appear in the Word doc. These options include:
• Keep Source Formatting - Reflects what you copied on to Clipboard. This is also the same as the CTRL-V command on your keyboard.
• Use Destination Styles - Copies the Excel table, but the Paste doesn't carry over the Excel specific features, like colored cells.
• Link & Keep Source Formatting - Similar to the first option, however, if changes are made on the original Excel spreadsheet, the adjustments will be reflected in Word too.
• Link & Use Destination Cells - Like Use Destination Styles, this option adds the linking capability.
• Picture - Captures a snapshot of Excel table and functions as a pasted image.
• Keep Text Only - Removes special formatting and pastes just the text from the Excel table.

How to Insert a Screenshot or Screen Clipping in PowerPoint

A PowerPoint deck is a prime example of an Office document that brings together many types of rich media. And when you have a presentation with more than 20 slides, importing files can be tedious and take a lot of time. To help ease the time on image importing, the built-in screenshot button can speed up your PowerPoint deck creation. Follow the steps below to learn more.

• In your PowerPoint presentation, select the Insert tab.
• Click Screenshot.
• A drop down menu will appear with the option to take a screenshot of a window open on your desktop, or take a screen clipping of only a portion of your screen.
• Select the option that suits your need.
• The screenshot or screen clipping will insert straight into your PowerPoint presentation.
• Adjust sizing and placement of the image as needed.

How to Recall Messages in Outlook

You click "Send" and instantly realize it was a mistake. The email isn't in your outbox anymore. What do you do?

If you're lucky, the conditions might be right to use the message recall feature in Outlook. The points below will walk you through the recall steps in Outlook 2013 (this option is not available in the Outlook Web App.):

• Open your sent item into a new window.
• Click the Actions button in the Move section.
• Select Recall This Message.
• If you want to recall your message altogether, so it will be deleted from the recipient's mailbox, click Delete unread copies and replace with a new message.
• If you want to fix a typo, add an attachment, etc. and resend the message, click Delete unread copies and replace with a new message.
• Click OK. If you kept the box checked to receive notification if the recall succeeded or failed, you will receive an automated email informing you of the status.

Ideally, the recipient of your message will open the recall notice, and the original message will automatically be deleted. But as mentioned, the conditions have to be right for the recall process to work.

Requirements for the recall process to be successful:

• Both users are on Exchange within the same organization. You can't recall messages sent to email addresses outside your organization.
• The recipient also must be using the Outlook desktop app, i.e. not Outlook Web App or a mobile version.
• The original message must have arrived in the Inbox of the recipient, i.e. not diverted or filtered with a rule.
• The original message must be unread, and the recipient must open the recall notice first, before the original message.

As you can see, many conditions have to be just right. Microsoft offers a thorough breakdown of some of the possible scenarios when using recall.

Regarding meeting all these conditions, the MSOutlook.info site mentions, "Chances are however that you won't that lucky. To counter that, you could create a rule which delays sending all messages for 1 or 2 minutes so you have some time to react without seriously slowing down your regular communications. You can set it up 120 minutes if you want but that might be less practical."

How to Add a Signature to a Word Document

There are many documents that require a signature like a lease, medical forms, and basically anything official in your life. When the day comes to when you need to make a document that requires a signature, here are the steps to add a signature to your Word document.

• Place the cursor in your Word document where you want to insert a signature.
• Click the Insert tab.
• Select Signature Line.
• A menu will appear.
• Select OK.
• A signature line now appears in your Word doc.
• When you're ready, share the document with the person from which you're requesting the signature.

If you are the person receiving the document, these are the steps to follow to complete a signature.

• Once you have opened the document, you will be notified at the top menu bar with a yellow alert bar that states that this document requires a signature.
• In the yellow alert bar, select View Signatures.
• To add your signature, double click next to the "X".
• A Sign box will appear where you can type you name out to use as your signature, or you can upload an image you might have stored on your computer.
• When you are ready to sign, click Sign.

The Pilgrims Would Approve: Three Things to Do Before You Leave For Christmas

The holidays have arrived. As we're all preparing to head out for Christmas and check out for a little bit, there's always a list of to-dos in the office before shutting down. To make sure everything is taken care of in your work life, we want to help you make sure your break away from the office is stress free and that you feel refreshed and invigorated for your return to the office.

## Sharing [your calendar] is Caring

Before you leave the office, make sure to share your travel plans with the people who matter. In Outlook, you are able to share your calendar both internally and externally. Letting your team know your travel plans can be helpful to alert them of when you will be near a computer, or are out of the office and unable to check email frequently. In Outlook, you are also able to share with external parties, such as customers or colleagues that work in different organizations. You are able to customize your sharing settings too, depending on how much others need to know.

## Setting Up Great Expectations

Alerting the people that matter the most of your calendar is taking care of your most important contacts. However, setting an away message can let other people know of your whereabouts and availability. It's funny how timing works sometimes and the response you've been waiting on arrives when you're away. Luckily, setting up an away message can buy you some time on returning an important email and set the expectation of when you are able to reply.

## Win the Turkey Race by Scheduling Emails

Deadlines tend to often hover around inconvenient times. To make sure you're fired up and ready to go, schedule an email to stay ahead of the game. Need to followup on a few items the day you get back from Christmas? Schedule an email today to make sure your ducks are in a row with an upcoming project and that come Monday, you'll have no problem making your deadline.

How to Change the Font of Your Outlook Messages in Office 365

Sick of Calibri? You don't have to stick with Office 365's standard set of fonts in your documents or messages. It's a snap to change the font of an individual email message or even the default font for all new email messages you create.

In individual messages, simply highlight the text you wish to change, and select a new font style from the menu.

To change the default font in Outlook 2013/2016:

1. From the File tab, select Options.
2. Select Mail.
3. Click Stationery and Fonts.
4. Make your desired changes to new mail messages, replying or forwarding messages, and/or composing and reading plain text messages.

## To change the default font in Outlook on the Web:

1. From your gear menu, select Options.
2. Click Message format under the Layout menu.
3. Make your desired changes in the Message font window.
4. Click Save.

How to Spell-Check Email Messages in Outlook for Office 365

Even if you're a spelling bee champion, you're bound to make typos from time to time, especially in our fast-paced modern way of working. Not only can you check the spelling of long Word documents and the like, you can also spell-check Outlook email messages with just a few steps.

How to check spelling in Outlook 2013/2016

1. To check an individual word, simply right-click and choose from the options to change, ignore, or add the misspelled word.
2. To check an entire email, go to the Review tab and select Spelling & Grammar.

How to check spelling in Outlook on the Web

Unfortunately at this time there isn't the capability to check spelling built into Outlook on the Web. However, you can configure your computer and/or your browser to automatically check the spelling as you type.

## How to Add a Watermark to a PowerPoint Presentation

Some of the most confidential data is presented in a PowerPoint deck and one method to imply that there is confidential data is to add a watermark to the slides. Oftentimes, we need reminders that a document is "Internal Only," a "Draft," or "Not for Distribution," and adding a watermark is a helpful alert your audience.

First, we will begin by adjusting the slide master:

• Click the View tab. Select the top slide.
• Select Slide master.
• Go to the Inserttab.
• Add a text box in middle of the slide.
• Type in text you want to be watermarked.
• Resize, rotate, and adjust the text as you see fit.
• Now to make the text look faded and more like a watermark, select the Format tabClose the master view in the top menu bar.
• Click the pop out menu for Format Shape. A menu will appear on the right.
• Click the Text options icon in the format shape menu.
• Under Text Fill and Outline, you can adjust the transparency of the text here.
• Adjust the text transparency as you see fit.
• Now, the watermark should be visible on text heavy slides.

## How to Share Large Files in Office 365 Using OneDrive for Business

If you're trying to share a video, program, or other large file, you've probably encountered the dreaded error when trying to send via Outlook: "This file is too large to send as an attachment."

Even if your admin has allowed for you to send large attachments, sometimes Outlook can't handle it. Luckily, Office 365 can come to the rescue!

The basic solution to this issue is to save your file to OneDrive for Business and share with your intended recipient directly from there. ODfB can handle it, which makes sharing large files a breeze.

Follow the instructions listed below:

• Select the Share icon from the top bar, or right-click and select Share.
• Enter the recipient's name in the Invite people field, select your desired editing permissions and sign in requirement, and enter an option message if you're sending an email invitation. Alternatively, you can generate a sharing link and send it via the platform of your choice.

## Creating a New Document in VBA

One of the common things done during a macro is to create a new document. For instance, your macro could need the new document to hold processed text, or a different version of the document on which you are working.

To create a new document, simply include this line in your VBA macro:

`Documents.Add `

This creates a new document, based on Normal.Dot, adds it to the Documents collection, and makes the document active. This is the same as clicking the New button on the toolbar. If you want to create a new document based on a different template, simply use this command:

## Sparklines

Excel Sparklines were introduced in Excel 2010 and are great for displaying the trend of data over time and making sense of a sea of numbers. Just take this before and after example:

### Before

A sea of numbers that would take a long time to analyse and compare:

### After

Instantly we can get a feel for the numbers without having to read each row and compare them against one another. In fact we don't even need to see the month values, just a total and an average per Salesperson would be enough to support the Sparklines.

And because Sparklines fit in a single cell they're ideal for dashboards, which have limited space.

The term Sparkline was coined by Edward Tufte and he describes them as "intense, simple, word-sized graphics".

## Excel Sparklines - Types

There are 3 Sparkline types to choose from including Line, Column and Win/Loss, which you'll find on the Insert tab of the ribbon:

## Inserting Sparklines

Excel Sparklines occupy a cell so the first thing you should do is select the cell, or cells, you want them inserted into (this will prepopulate the Location Range in the dialog box) > Insert tab > choose the Sparkline type you want.

This will open the 'Create Sparklines' dialog box and you can select the range of cells containing the data for your Sparklines:

Notes:

1. The data can be on different sheet to your Sparkline.
2. You can change the Location Range if you forget to first select the cells where you want your Sparklines to go.
3. Your Data Range can be organised horizontally or vertically, although I find it clearer if it's arranged horizontally like in the example file.

## Formatting Sparklines

Clicking on a Sparkline will activate the contextual Sparkline Tools tab for Design:

Here you can edit the location and source data, add markers for various points, choose from pre-set Styles, modify colours and axis settings.

To activate the Sparkline: Design contextual tab simply select a cell containing a Sparkline.

## Grouping

When you select a range of cells before inserting them they are automatically grouped. You can tell they're grouped because when you select one, those in the group have a blue border around them.

When they're grouped any formatting changes are automatically applied to all Sparklines in the group.

## Sparkline Tips

• Less is more; While Sparklines occupy a single cell you can still use that cell to enter data, apply Conditional Formatting and other things you'd typically use a cell for, but don't get carried away. Too much in the one cell could result in the message being lost.
• Use adjacent cells for more detail as opposed to using the cell which contains the Sparkline. Instead, I think you're better off using adjacent columns for any additional data like the YTD total or average etc.
• Use Markers sparingly; try highlighting the lowest or highest points if you think it will add value, but be careful it doesn't end up looking like a Christmas tree!
• Give them space - adjust the row height to give them more space and make it easier to read.
• Fix Axes - particularly with column Sparklines where comparisons from one set of sparklines to the next can be misleading if they all start from a different point. I recommend setting them to start at zero (assuming there aren't any negative values in the data).
• Sparklines can also occupy columns in Tables, and when new rows are added to the Table the Sparkline also gets copied down automatically.
• Copy & Paste; you can copy or cut and paste a Sparkline to other cells, and you can use the Fill Down tool to add more Sparklines.

Copy and Paste Visible Cells Only

Have you ever wanted to only copy or paste visible cells?

For example, below I have a table containing outstanding customer invoices. I want to insert a formula in column E to insert an overdue fee for invoices outstanding longer than 31 days:

However, I don't want the formula on the subtotal rows (row 5, 11, 13, 19, 21 etc.)

I can use the Group buttons to quickly hide the subtotal rows. I've entered my formula in cell E2 and I just need to copy it down:

Note: I know I could have used filters to hide the Subtotal rows, but for whatever reason I don't want to, so let's go with it.

## Paste Visible Cells Only

The problem with a regular copy and paste is Excel will also paste to the hidden cells, so I have to select the visible cells first. I can do this with Go To Special:

1. Copy cell E2 to the clipboard - just select it and press CTRL+C
2. Select the range you want to paste to. In my case E3:E51
3. Press CTRL+G to open the Go To dialog box and then click 'Special' in the bottom left:

1. In the Go To Special dialog box select the 'Visible cells only' button and click OK.

Notice how each group of cells are individually selected:

1. You can go ahead and press CTRL+V to paste the formula into the visible cells. I've unhidden the subtotal rows in the image below so you can see the magic:

Tip: Instead of copying and pasting, I could enter the same formula in all the selected visible cells using steps 2 to 4 above, then for step 5: type in the formula and press CTRL+ENTER to enter them all in one go.

## Copy Visible Cells Only

Copying cells in a filtered table will only copy the visible cells by default, but if you have hidden rows or columns (as opposed to filtered), then Excel will copy the hidden ones too.

For example, you can see in the data below that row 3 is hidden:

We can use the same technique to only copy visible cells:

1. Select the range A2:E9
2. CTRL+G to open the Go To dialog box
3. Click 'Special'
4. Select 'Visible cells only'

You can see there is a subtle line between rows 2 and 4 indicating row 3 is not selected:

1. Press CTRL+C to copy and then go ahead and paste the cells where you want. You'll notice they are pasted as a contiguous range of the 7 copied rows:

Copying Subtotals

Excel has a handy feature that allows you to automatically add subtotals to a data list. You may be wondering, once the subtotals are in place, how you can copy the subtotals to a different worksheet. This is actually rather easy to do, if you follow these steps:

1. Add your worksheet subtotals as you normally would.

2. Collapse the information in the list so that only the subtotals are showing. (Click on the small 2 in the outline levels shown at the top of the gray area at the left of the worksheet.)

3. Select the range of cells containing the subtotals you want to copy. (Do not select complete rows or columns; just select the range of cells.)

4. Press F5. Excel displays the Go To dialog box.

5. Click on Special. Excel displays the Go To Special dialog box (see picture).

6. Make sure the Visible Cells Only option is selected.

7. Click on OK. Excel selects just the visible information from the range you specified in step 3.

8. Press Ctrl+C to copy the rows to the Clipboard.

9. Select the cell where you want to paste the subtotals.

10. Press Ctrl+V to paste the information.

Problems with Custom Views

Custom views can be a great help in presenting data in a workbook. If you are using custom views, you may be surprised at some point by an error message that says "Some view settings could not be applied."

Don't despair if you see this message; it simply means that protection has been applied to one or more of the worksheets involved in the custom view. With protection enabled, any view settings that would violate the protection settings will not be applied and the message is displayed.

The solution is to either modify the view so it doesn't use any of the protected worksheets, or remove the protection from the worksheets used in the view.

How to Change the Background of a PowerPoint Slide

When you're working with PowerPoint slides or an overall theme, there's a good chance you will want to change the background from the standard white to something more customized. It's a great way to add visual interest to your PowerPoint presentation.

Backgrounds come in a few styles: solid, gradient, picture or texture, and pattern.

To change the background of a slide or slides, follow the instructions listed below:

1. Go to the DESIGN tab.

2. Click the Format Background button.

3. In the Format Background Pane that appears, select the type of fill for your slide background. It will immediately apply to the slide (no need to click an option like "apply.")

4. Depending on your choice, modify the additional options. For example, if you choose a gradient fill, you can modify the type, direction, angle, gradient stops, transparency, and so on.

5. Select Reset Background if you'd like to remove the background formatting. Select Apply to All if you want to apply your choices to all slides in the presentation.

Adding a Little Animation to Excel

Anyone who says that Excel is boring obviously doesn't know that you can add animation to your workbooks. You can spice up your life by turning on animation for the feedback you receive from Excel. For instance, instead of seeing a new column immediately appear when you add it, with animation turned on the column will appear to "slide" into position, slyly moving existing columns to the right.

If you want to turn on feedback animation in your system, follow these steps if you are using Excel 2007 or 2010:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Advanced.
3. Scroll through the available options until you see the General section.

1. Make sure the Provide Feedback with Animation check box is selected.
2. Click on OK.

This option is available only in Excel 2007 and Excel 2010. It was removed entirely from Excel 2013.

Comments and revisions got a makeover with Word 2013, with a much more streamlined look and feel and improved functionality. A key collaboration and communication component of Word, comments now include long-requested functions like replying and marking as complete.

The video above provides the steps to add and reply to comments in Word 2013, as well as mark them as complete. You can also follow the steps below.

To Add A Comment:

1. Select the relevant text you are commenting on-sentence, paragraph, etc.

2. From the REVIEW tab, click New Comment.

3. A comment bubble with your name will appear. Type your comment in the field.

4. Click away or press Escape to complete the comment.

If you need to add to or edit your comment, simply click on the text and continue typing in the text field.

To Reply To A Comment:

1. Hover over the comment you wish to reply to.

2. Click the reply icon in the upper right corner of the comment box.

3. A reply field will appear with your name attached.

4. Type your reply in the field.

5. Click away or press Escape to complete the reply.

To Mark A Comment As Complete:

1. Right-click on the comment

2. Select Mark Comment Done.

There is a way to selectively load add-ins for specific worksheets, but it involves the use of macros attached to the Workbook module for the specific worksheets. Follow these general steps:

1. Load the worksheet for which you want a specific add-in loaded.
2. Press Alt+F11 to display the VBA Editor.
3. Double-click on the "This Workbook" object in the Project Explorer. Excel opens a code window for This Workbook.
4. Place the following macros in the code window:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_Open()

End Sub

1. In the code, change the name of the add-in ("Add-In Name") to the real name of the add-in you want to use with the workbook.
2. Close the VBA Editor.
3. Save your workbook.

How to Create a Survey Using Excel

You can use Excel Online to create a survey with a shareable link that saves responses in a back-end spreadsheet, allowing you to analyze your data however you wish.

To create your survey:

1. Click the new button and choose Excel survey.
2. Enter a survey title. This is for internal use, and will store the responses, so name it however you prefer.
3. In the Edit Survey window that appears, enter a title and description for the survey.
4. Click into the Enter your first question here field.
5. Enter a survey Question, and an optional subtitle.
6. Choose a Response Type:
• Text: for short written responses
• Paragraph text: for longer written responses
• Number: for numerical responses
• Date: for answers you can sort by date
• Time: for answers you can sort by time
• Yes/No: for yes or no responses
7. Choice: for a drop-down menu the survey taker can choose from
8. Check the Required box if the question is not optional.
9. Enter a Default Answer that will be pre-filled for the survey taker.
10. Click Done.
11. To add more questions, click + Add New Question and repeat steps 5-10.
12. Once you've completed adding questions, click Save and View.
13. Review your survey, and click Share Survey to get your sharing link.

You also may choose to view and test your survey, using the sharing link, before you distribute it to others. This will help verify that the survey is working correctly and the questions will collect the type of answers you need.

When others take your survey, their responses will be stored in your Excel Online spreadsheet. You can do whatever you want with the data, and it's a good practice to consider your end goals before you create the survey, as it may affect how you write and structure the questions.

Share Your Outlook Calendar With People Within Your Organisation

1. From your calendar, select the SHARE button.

2. Enter the name(s) or email address(es) of the person or persons you want to share your calendar with.

3. From the drop-down menu that appears, choose how much information you want to share. According to Microsoft, full details show the time, subject, location, and other details of all items in your calendar. Limited details show the time, subject, and location, but no other information. Availability only shows the time of items on your calendar and no other details. An editor can edit your calendar. A delegate can edit your calendar, and can send and respond to meeting requests on your behalf. For more information about editor and delegate access, see Calendar Delegation in Outlook Web App.

4. Optionally, edit the subject line of the invitation.

5. If enabled and necessary, select which calendar you want to share. (Note that if you share a calendar other than your primary calendar, you can give permission only for full details or editor access.)

6. Click Send.

Each recipient will receive an invitation with an option to add your calendar and share their calendar back. It also will include a URL that can be used to access the calendar. If they add your calendar, it will be displayed under People's calendars on their calendar page.

Entering Dates in Excel

Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:

• 12/15
• 12-15/12
• 15 Dec
• December 15, 2012

If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. You could also use dashes instead of slashes and Excel will still figure out you are entering a date.

Regardless of how you type a date, it is converted to a special serial number by Excel and stored internally in that format. How you see the date on your screen is a consequence of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.

Adding Text Boxes to Charts

When you create a chart from your Excel data, you may want to add text boxes to the chart for any number of purposes (copyright statement, explanation, notes, etc.). There are two quick ways you can add a text box.

The first method is to use the Drawing toolbar or, if you are using Excel 2007, the Insert tab of the ribbon. Simply make sure it is displayed, then click the Text Box tool. The mouse pointer changes to crosshairs, and you can click and drag to outline the text box you want created.

The second way to create a text box is to use the Formula bar. Make sure you select any part of your chart except a title or data label. Click in the Formula bar and start typing what you want to appear in the text box. When you press Enter, the text you typed is placed in a text box in the center of the chart. You can then move and resize the text box, as desired.

Excel Tip - Fixing Odd Sorting Behaviour in Excel

Sometimes when you try and sort data in Excel, it does not sort in the order that you expect. Excel interprets the information in each cell. When you enter information in a cell, Excel tries to determine if it is a number, a date, or text.

It just so happens that Excel is "guessing wrong" when it comes to some data.

When you enter 311, Excel considers that to be a number, so it treats it as a number. Similarly, if you enter the text "True," Excel considers that a Boolean value - which is a number.

When performing a sort, Excel first sorts by the data type and then within the data type:
- 112 and 702 are numbers
- 3 of Hearts and 38 Special are text because they don't consist of only digits.

When sorting by the column, the 311 shows up in two different places because the group name was parsed by Excel in some instances as a number and in other instances as text.

To understand how to correct the odd behaviour, it is important to understand that the behaviour isn't really odd; it is the logic Excel uses.

First, if you sort in ascending order, the values in your cells will be sorted in these data types:

•   Numbers in increasing value (1, 2, 3, etc.)
•   Text in alphabetic order (a, b, c, etc.). If the text begins with a number (as in 38 Special), then the 3, as text, appears before the ABCs.
•   Logical values (False, True)
•   Error values (#DIV/0!, #N/A, etc.)
•   Blanks

You can better see the data types that Excel assigns to various cells by removing any explicit alignment in the cells - by default the text values are left-justified, numbers right-justified, and Boolean and error values centered.

To get things to sort the way you want, you just need to make sure that all the cells in a column contain the same type of data - In the cells being sorted as numbers (like 311), edit the cell to place an apostrophe before the first digit in the number. This tells Excel you want the cell's value treated as text. You can also do the same thing with "True."

Excel Tip - Calculating Fractions of Years

One of the types of data that Excel allows you to store is, of course, dates. At some point you may wish to perform some calculations with the dates in your worksheet. It is not uncommon to need to figure out the percentage of a year represented by the difference between two dates.

Excel allows you to calculate this easily using the YEARFRAC worksheet function.

To use the function, all you need to do is provide two dates and a value that specifies how Excel should calculate the fractional year:
=YEARFRAC(DateOne, DateTwo, Basis)

- The dates used by YEARFRAC can be either static dates, or they can be references to cells that contain dates.
- The Basis value ranges between 0 and 4, with 0 being the default. The following are the different meanings for the Basis:

Basis Meaning
0 US 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Excel Tip - Formatting PivotTables

Maintaining Formatting when Refreshing PivotTables

PivotTables provide a great way to analyze large amounts of data and pull out the summarizations that you need. Once you have the PivotTable displaying the values you need, you can then format the table to make the data presentable-for a while. You see, when you update the data on which the PivotTable is based, and then refresh the PivotTable, all your formatting work may go away.

The way around this is to follow these steps:

1) Make sure that your PivotTable displays the values you want.
2) Format the PivotTable in whatever way desired.
3) Select a cell in the PivotTable.
4) Display the Options tab of the ribbon.
5) Click the Options tool in the PivotTable group. Excel displays the PivotTable Options dialog box.
6) Make sure the Layout & Format tab is displayed.
7) Make sure the Preserve Cell Formatting On Update check box is selected.
8) Click OK.

Now, when you refresh the PivotTable, your previously applied formatting should remain on rows and columns previously in the PivotTable. If the refresh results in new rows being added to the PivotTable, then you will still need to format those, unless you are using an AutoFormat.

Excel Tip - Converting a Reference to a Hyperlink Into a Hyperlink

If you have a hyperlink in a cell (such as cell A1) and then you use a formula in another cell that references that hyperlink, the result of that formula is not a hyperlink. For instance, suppose cell B1 contains this simple formula: =A1

The result of that formula will not be a hyperlink, even if cell A1 contains a hyperlink. The reason is that the formula extracts the value of the referenced cell, which is the text displayed in A1.

If what is displayed in cell A1 is a URL, then you could modify your formula just a bit to result in a hyperlink: =HYPERLINK(A1)

If cell A1 does not contain a URL, or if it is a hyperlink where the displayed text is different then the underlying URL, then the HYPERLINK function won't work as expected.

Excel Tip - Changing Chart Size

There are two types of charts that you can create in Excel-embedded charts and chart sheets. A chart sheet occupies an entire page. An embedded chart appears on the same page as your worksheet data.

If you are working with an embedded chart, you can change the size of the chart to any size you want. You cannot directly change the size of a chart sheet; it is set to be a single page. You can modify the printed size of a chart sheet, however.

You change the size of an embedded chart as you would any other graphical object in Excel:

1. Click once on the chart. Handles appear around the chart border. As you move the mouse pointer over these handles, they change to sizing arrows.
2. Click and hold the left mouse button and drag a sizing handle until the graphic is the size you want. The arrow heads on the mouse pointer indicate the direction which you can move the border.
3. Release the mouse button. The chart is resized and reformatted.

Popular Courses