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:
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:
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:
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:
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:
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:
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"
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.
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:
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:
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:
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:
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:
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()
ActiveWindow.FreezePanes = True
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.
ActiveWindow.FreezePanes = True
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.
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:
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:
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:
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:
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:
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
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)))
If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then
tmpSum = tmpSum + PartStock(i)
AddPrtQty = tmpSum
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:
MsgBox "This is my comment"
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.
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.
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:
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:
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.
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
Application.ScreenUpdating = True
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).
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.
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:
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.
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
.EnableEvents = False
.Undo ' This undoes the change the person made
.EnableEvents = True
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
MsgBox "Worksheet is protected."
MsgBox "You are free to edit now."
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 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.)
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.
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'' 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:
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.
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:
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:
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.
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.
To quickly increase or decrease your font in PowerPoint, select the font you want to adjust and hit CTRL + Shift + < or >.
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.
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.
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.
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:
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.
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:
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:
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.
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):
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.
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:
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:
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.
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.
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.
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.):
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:
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.
If you are the person receiving the document, these are the steps to follow to complete a signature.
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.
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.
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.
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:
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
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.
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:
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:
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:
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:
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:
A sea of numbers that would take a long time to analyse and compare:
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".
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:
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:
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.
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.
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.
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:
Notice how each group of cells are individually selected:
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.
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:
You can see there is a subtle line between rows 2 and 4 indicating row 3 is not selected:
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:
This option is available only in Excel 2007 and Excel 2010. It was removed entirely from Excel 2013.
Add, Reply To, And Complete Comments In Word 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.
Automatically Loading Add-ins
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:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Add-In Name").Installed = False
Private Sub Workbook_Open()
AddIns("Add-In Name").Installed = True
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:
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:
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:
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:
0 US 30/360
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: