Excel Software Tips

Software Tips Home

 

Date

Tip

2/6/18

Don't Make This Common Pivot Table Mistake

One of the biggest mistakes new Pivot Table users make is not including new rows and columns of source data in their Pivot Tables.

Thankfully there is an easy solution. It's as simple as formatting your source data as an 'Excel Table'.

 
Keyboard Shortcut Create Excel Table in Microsoft Excel 2007 2010 2013 2016 365
When you format your Pivot Table's source data as an Excel Table, it automatically expands to include new rows and columns as they are added, so you don't have to repeatedly update your source data's cell reference.

To format your source data as an Excel Table, click any cell in the data and press CTRL+T (or
from the Home tab click Format as Table or from the Insert tab, click Table).

As with all Pivot Tables, you will still need to hit (right-click) Refresh to update your Pivot Table with the added data.

1/10/18

Hiding Repeating Values In A Column

When you are working with lists in Excel, you may sometimes find that, after sorting, you have multiple rows with the same values repeating down one or more columns. If you are printing this list as a report, for aesthetic purposes, you may prefer to have only the first instance of each item shown.

One option is to clear the cells for all duplicate items below the first one. The problem with this approach is (a) is can be very time-consuming if you're working with a long list and (b) once you delete values from the cells, you won't be able to sort or filter your list.
 
I sometimes use this Conditional Formatting trick that I need to do only one time. After that, all future duplicate values will be hidden.**(see caveat below)
 
 
1) Select the range of cells in the column where you want to hide duplicates. In this example we have selected B4:B23. Note that cell B4 is the Active Cell;
 
Conditional Formatting White Font Hide Duplicate Cell 1 in Microsoft Excel 2007 2010 2013 2016 365

2) On the Home tab, click Conditional Formatting in the Styles group and then choose New Rule...;

3) Select the Rule Type 'Use a formula to determine which cells to format';

4) With Conditional Formatting, you always base your formula on the Active Cell in the current selection. In this case the Active Cell is B4. So in the formula field type =B4=B3. This formula tells Excel to check if the active cell's value is equal to cell above. If they are equal, the result of this formula is TRUE, therefore the formatting you specify will be applied to that cell;

5) Click the Format... button, click the Font tab in the Format Cells dialog. Select the Color dropdown, choose white (i.e. the same as the background color of these cells) from the color palette and click OK;


6) Click OK to close the Conditional Formatting dialog.

 
Conditional Formatting White Font Hide Duplicate Cell 2 in Microsoft Excel 2007 2010 2013 2016 365
Since your new Conditional Formatting rule has automatically applied a font color that matches the background color to the duplicate values, those cells will appear blank although the values are still in the cells (just look in the Formula Bar).
 
Conditional Formatting White Font Hide Duplicate Cell 3 in Microsoft Excel 2007 2010 2013 2016 365

 

** CAVEAT: Use this trick with caution. Keep in mind that you (or other users of the spreadsheet) may forget that there is data in the 'seemingly blank' cells and could cause problems.

Here are a couple of solutions I sometimes use to avoid this:

Solution 1: Make a temporary working copy of the sheet

Point to the sheet tab, hold down the CTRL key and drag it to the right. To avoid confusing the two sheets, add a Text box identifying the duplicate sheet. Now your original sheet remains untouched.
 
Copy Sheet Tab in Microsoft Excel 2007 2010 2013 2016 365

Solution 2: Toggle Conditional Formatting On and Off

To make it easy to turn a Conditional Format on or off, include an additional condition in your CF formula.

Edit *** your original Conditional Formatting formula with the following...
 
=AND($D$1="Y",Original_CF_Formula)

... where $D$1 is a cell where you type Y or N to turn ON or OFF your Conditional Formatting rule.

The AND function allows you to apply multiple conditions to your CF formula. In this case, when both condtions are TRUE, that is, when cell D1 equals Y and the active cell's value is equal to cell above, the AND function's result is TRUE and applies the Conditional Formatting.
 
Toggle Conditional Formatting On Off in Microsoft Excel 2007 2010 2013 2016 365

*** Note that, as I mentioned in a previous tip, editing a formula in the Conditional Formatting dialog can be tricky AND REALLY FRUSTRATING. You'll have to click in the formula box then press the F2 key to switch from Enter mode to Edit mode, which will allow you to use the arrow keys to move the cursor around the formula box.

 
Edit Conditional Formatting Formula With F2 Key in Microsoft Excel 2007 2010 2013 2016 365

 

 


When you are working with lists in Excel, you may sometimes find that, after sorting, you have multiple rows with the same values repeating down one or more columns. If you are printing this list as a report, for aesthetic purposes, you may prefer to have only the first instance of each item shown.

One option is to clear the cells for all duplicate items below the first one. The problem with this approach is (a) is can be very time-consuming if you're working with a long list and (b) once you delete values from the cells, you won't be able to sort or filter your list.
 
I sometimes use this Conditional Formatting trick that I need to do only one time. After that, all future duplicate values will be hidden.**(see caveat below)
 
CLICK HERE TO DOWNLOAD THIS PRACTICE FILE
 
1) Select the range of cells in the column where you want to hide duplicates. In this example we have selected B4:B23. Note that cell B4 is the Active Cell;
 
Conditional Formatting White Font Hide Duplicate Cell 1 in Microsoft Excel 2007 2010 2013 2016 365

2) On the Home tab, click Conditional Formatting in the Styles group and then choose New Rule...;

3) Select the Rule Type 'Use a formula to determine which cells to format';

4) With Conditional Formatting, you always base your formula on the Active Cell in the current selection. In this case the Active Cell is B4. So in the formula field type =B4=B3. This formula tells Excel to check if the active cell's value is equal to cell above. If they are equal, the result of this formula is TRUE, therefore the formatting you specify will be applied to that cell;

5) Click the Format... button, click the Font tab in the Format Cells dialog. Select the Color dropdown, choose white (i.e. the same as the background color of these cells) from the color palette and click OK;


6) Click OK to close the Conditional Formatting dialog.

 
Conditional Formatting White Font Hide Duplicate Cell 2 in Microsoft Excel 2007 2010 2013 2016 365
Since your new Conditional Formatting rule has automatically applied a font color that matches the background color to the duplicate values, those cells will appear blank although the values are still in the cells (just look in the Formula Bar).
 
Conditional Formatting White Font Hide Duplicate Cell 3 in Microsoft Excel 2007 2010 2013 2016 365

 

** CAVEAT: Use this trick with caution. Keep in mind that you (or other users of the spreadsheet) may forget that there is data in the 'seemingly blank' cells and could cause problems.

Here are a couple of solutions I sometimes use to avoid this:

Solution 1: Make a temporary working copy of the sheet

Point to the sheet tab, hold down the CTRL key and drag it to the right. To avoid confusing the two sheets, add a Text box identifying the duplicate sheet. Now your original sheet remains untouched.
 
Copy Sheet Tab in Microsoft Excel 2007 2010 2013 2016 365

Solution 2: Toggle Conditional Formatting On and Off

To make it easy to turn a Conditional Format on or off, include an additional condition in your CF formula.

Edit *** your original Conditional Formatting formula with the following...
 
=AND($D$1="Y",Original_CF_Formula)

... where $D$1 is a cell where you type Y or N to turn ON or OFF your Conditional Formatting rule.

The AND function allows you to apply multiple conditions to your CF formula. In this case, when both condtions are TRUE, that is, when cell D1 equals Y and the active cell's value is equal to cell above, the AND function's result is TRUE and applies the Conditional Formatting.
 
Toggle Conditional Formatting On Off in Microsoft Excel 2007 2010 2013 2016 365

*** Note that, as I mentioned in a previous tip, editing a formula in the Conditional Formatting dialog can be tricky AND REALLY FRUSTRATING. You'll have to click in the formula box then press the F2 key to switch from Enter mode to Edit mode, which will allow you to use the arrow keys to move the cursor around the formula box.

 
Edit Conditional Formatting Formula With F2 Key in Microsoft Excel 2007 2010 2013 2016 365

11/14/17

Because Excel is used for such a wide variety of tasks, you may occasionally find yourself typing paragraphs of text into your worksheets. Since Excel allows word wrapping in a cell, you can easily type a paragraph or more into a single cell. However, there may be times when word wrapping is not practical because the increased row height also affects adjacent cells.
 
Wrapping Text In One Column Adjusts Row Height Of All Columns in Microsoft Excel 2003, 2007, 2010, 2013, 365

One way to avoid this is to type your paragraph into several cells going down the column. If you have ever done this, no doubt, you have also had to make changes to your text or re-adjust the column width and then found yourself cutting and pasting text from one row to the next to make it fit on each line.
 
Another example would be if you copy some text from a web page or elsewhere and paste it into your worksheet. You may find that all of the text is pasted into one cell and extends to the right off the screen but you want to spread the text over several rows and columns.
 
Well, you'll be happy to know that there is a little-known option, practically hidden in Excel, that can be a godsend whenever you find yourself trying to redistribute text over several rows. It's called Justify.

Now let's see what Justify does...
 
For Justify to work, your text must be in one or more cells in a single column. You can spread the text to display across several columns and rows but the actual text will be stored in the leftmost column only. You can also include blank cells (i.e. paragraph breaks) in your text.
 
Believe me, using Excel's Justify command is really useful and really easy! Please follow along with these examples and see for yourself.

Distributing Text Down A Single Column

1) Open a new workbook;

2) Type a couple of sentences in cell A3 (a single cell) or copy some text from a web page and paste (right-click the cell and select Paste Special, Text, OK) it into cell A3. Go ahead. Try it now. When you Paste Special, Text, the text should be in one cell but will overflow into the blank columns to the right;

3) Adjust the width of column A to 50;

4) Now, highlight cells A3:A10;
 
Distribute (Justify) Text Across Multiple Rows And Columns in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
5) On the Home tab, in the Editing group, click the Fill command (the small button with the downward pointing arrow) and select Justify. All of the text from the 'active cell' gets evenly distributed down column A;
 
Text Evenly Distributed To All Cells In The Selected Range in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

To see that work again, change the width of column A (wider or narrower) and repeat step 5. Isn't that cool?

Distributing Text Across Multiple Columns and Rows

As I said before, the text must all be in one column. But rather than adjusting the column width as in the example above and fitting the text into one column, instead you can have the text appear to be distributed across several columns.

Try this.

1) Resize column A to it's original width 8.43;

2) Highlight cells A3:D12;

3) On the Home tab, in the Editing group, click the Fill tool and select Justify. Notice that, although the text is still contained only in the cells in column A, it now appears to be distributed across the columns you originally selected.
 
Text Distributed Across Multiple Columns And Rows in Microsoft Excel 2003, 2007, 2010, 2013, 365

Now, if you need to edit the text in cell A3 but still want to keep it displayed within columns A to D, it's just a simple matter re-selecting cells A3:D12 and redistributing using the Justify command. This sure beats all that cutting and pasting for each row.

Combining Text from Multiple Rows into One Row

Justify also works in reverse. By selecting more columns in your distribution range, you can combine text from multiple rows into fewer, or just one, row(s).  But keep in mind that the maximum number of characters that can be contained any single cell after the text has been justified is 255 (see note below).

1) Select cells A3:V8;

2) On the Home tab, in the Editing group, click the Fill tool and select Justify. The text from the cells in column A is now redistributed to fit within the new range you have selected. And since you selected more columns, the text fits on fewer rows. If some of the text gets truncated, click Undo (CTRL+Z) and include fewer columns in your distribution range.
 
Combine Text From Multiple Rows Into One Row in Microsoft Excel 2003, 2007, 2010, 2013, 365

A few things to keep in mind about using Excel's Justify command
 

  • The maximum number of characters you can have in one cell for Justify to work is 255. Extra characters will be truncated. If in doubt, use the LEN function to count the number of characters in a cell e.g. =LEN(A3).
  • If you need to use Justify (distribute) text containing more than 255 characters, first split the text into multiple cells (in the same column) so that neither cells contains more than 255 characters, then select a range that includes these cells in the leftmost column and Justify them.
  • If you need to use Justify (combine) more than 255 characters on fewer rows, make sure your distribution range is narrow enough that each row/cell after justifying will accommodate no more than 255 characters.
If the range you select isn't large enough to accommodate the text when justified, you will get a warning message allowing you to abort if necessary, otherwise it may overwrite data in the cells below.
10/3/17


Maybe you have tried using fractions in Excel and were left a little confused when dates started popping up.

The key to entering fractions in Excel is, you need to type a whole number, a space and then the fraction (e.g. 2 3/4).

For fraction values of less than 1 (e.g 1/2), you must enter a zero in place of the whole number, a space and then the fraction (e.g. 0 1/2). If you enter only the fraction part, Excel will likely interpret it as a date and display it as '02-Jan', unless you've already formatted your cell(s) to be displayed as fractions.

 
Working With Fractions in Microsoft Excel 2007 2010 2013 2016 365

To format cells with a fraction format, from the Home tab, select the number format dropdown in the Number group and choose Fraction.

Note that, even though the cell displays the number in a fraction format (e.g. 2 3/4), if you look in the Formula Bar you will see the decimal equivalent (e.g. 2.75). This enables you to do simple calculations with fractions even though the cells are formatted as fractions.

The cool thing about this is that when you enter a value that Excel interprets as a fraction, the fraction is automatically reduced to its smallest denominator (e.g. 4 12/16 will be displayed as 4 3/4).

To override this default format, open the Format Cells dialog (CTRL+1) and click Fraction. You will see several options available for formatting fractions.


 
Fraction Format Options in Microsoft Excel 2007 2010 2013 2016 365

8-15-17

Repeat That Last Action With Only One Finger

Here's a simple, quick tip that I use quite often but I find that most Excel users don't know about it.

Whenever you want to repeat the last action that you have just taken, simply select the next cell or range where you want to repeat it and press the F4 key.

You will be amazed at how many different tasks you can use this simple little trick to make things easier for you.

I often use this shortcut for inserting (or deleting) rows: insert a row using the mouse, use the arrow keys to move the active cell where you want to insert the next row and press F4. Move down again and hit F4 to insert the next row. Repeat as often as you want.

In my previous career, I used this a lot when I needed to crosscheck items on my worksheet with a paper report. To apply a yellow background color to cells that I want to highlight when my two hands are busy holding my paper and pen (or typing on the number keypad), rather than having to drop my pen, grab the mouse, point and click on a Fill color, I just use the arrow keys to move to the cell and hit the F4 key (without even dropping my pen).
 
This may not seem like such a big deal but when you need to repeat an action over and over, you will really appreciate the simplicity of the F4 key.
Just try it and you will see.

7/25/17

Subtotal Dates By Month and Year, Ignoring the Day

There may be times when you want to subtotal your data by month and year, however simply Subtotaling a column of dates won't work because that will create a subtotal for each day.
 
Microsoft Excel Trick: Subtotal dates by month
Here's a trick you can use to create subtotals for each month while ignoring the day...
 
1) Sort your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;

2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown(or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015);


Subtotal Dates By Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365
3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day;
 
Dates Subtotaled By Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365

4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year.
 
Display Subtotals For Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365
 


6/13/17

Backtrack Through Your Hyperlinks

 

If you use lots of hyperlinks in your workbooks like I do, you too may be frustrated when you can't easily backtrack to the previous hyperlinks you clicked.

When you click hyperlinks that take you to other sheets and then you want to retrace those previous links, you will typically have to manually navigate your way back, that's IF you can remember the order you followed.
 
Go Back To Previous Hyperlink in Microsoft Excel 2007 2010 2013 2016 365

You will be happy to know that Excel has a Back button that allows you to backtrack through previously clicked hyperlinks. By default, this button is not on the Ribbon or Quick Access Toolbar (QAT) but you can add it quite easily.

I  don't remember if the Back command is in all recent versions of Excel but do I know that it is in Excel 2013 and 2016. Unfortunately, the 'Excel for Mac Office 365' version is missing this command (so I've been told).
 
Add Back Button To Quick Access Toolbar in Microsoft Excel 2007 2010 2013 2016 365
Add the Back button to your Quick Access Toolbar
 
1) Right-click on the Ribbon and choose Customize the Quick Access Toolbar;

2) From the 'Choose commands from' dropdown, select All Commands;

3) Scroll down the list of commands and select Back (with a left-pointing arrow);

4) Click the Add>> button and click OK.

 
Click here to download the Practice File

Now, for example, when you click a link to go to the March sheet, then click another link to go to the June sheet, then a link to September, and then December, you can now click the Back button on the QAT to quickly retrace your steps back through the September, June and January sheets.
 
Click Back To Previous Hyperlink in Microsoft Excel 2007 2010 2013 2016 365

5/23/17

Round Numbers To The Nearest Multiple

Use MROUND Function to Round Number To Specified Multiple in Microsoft Excel 2007 2010 2013 2016 365

When you need to round a number to a specified multiple, such as rounding a price to the nearest 5¢ or rounding a time to the nearest 15 minutes, Excel's MROUND is the answer.

For example, several years ago in Canada our 1 cent coin was discontinued, so most cash transactions are now rounded to the nearest 5 cents.

Syntax for the MROUND Function:
=MROUND(number,multiple)


There are two arguments in the MROUND function. number is the number that you want to round. multiple is the multiple to use when rounding.

The MROUND function rounds a number to the nearest multiple of the second argument. For example, in the above example, all retail prices have to be rounded to the nearest 5¢. To calculate the retail prices for all products we use this MROUND formula...
=MROUND(C4,0.05)


Using MROUND for Time
You can also use the MROUND function to round a time to a specified increment. If you want to round a time to the nearest 15 minutes, use the formula...
=MROUND(B5,"0:15")

Round Time To Nearest Multiple in Microsoft Excel 2007 2010 2013 2016 365


Using MROUND with Negative numbers
Keep in mind that both arguments in the MROUND function must have the same sign. Therefore, if cells referenced by MROUND have a mix of positive and negative amounts, you will need to multiply the second argument by the sign of the first argument using the SIGN function.

In the example below, =MROUND(A5,0.25*SIGN(A5)) will work for both positive and negative values. The SIGN function returns a 1 if the sign is positive and -1 if the sign is negative.

Using MROUND With Negative Numbers in Microsoft Excel 2007 2010 2013 2016 365

 

4/18/17

Access Your QAT Commands with Keyboard Shortcuts

Do you have lots of commands on your Quick Access Toolbar (QAT)? Your QAT may be located above the Ribbon but I like to keep mine below the Ribbon.
 
Quick Access Toolbar Commands in Microsoft Excel 2007 2010 2013 2016 365

When your hands are already on your keyboard, it is often easier to carry out a command using a keyboard shortcut rather than reaching for your mouse.

When you press the ALT key, numbers and letters appear on the various tabs and commands on the Ribbon and Quick Access Toolbar. These are called 'alt keys' or 'access keys'.
 
Press Alt For Access Key Shortcuts in Microsoft Excel 2007 2010 2013 2016 365


You'll see that the commands on your QAT have 1-digit numbers assigned to the first nine commands, 2-digit numbers for the next nine and, if you have a lot of commands on your QAT, you may see 2-digit number and letter access keys on other QAT commands.

To carry out a command from your keyboard, simply press the ALT key then type the corresponding Access Key displayed next to the QAT command. For access keys with 2 digits, simply type one then the other.

For commands that you use frequently, you may find that using this ALT-key-shortcut method is easier than moving your hand back and forth between the mouse and keyboard.

3/21/17

Ensure Data Entered Is The Correct Length

To ensure that the correct number of characters are entered for data such as SKU #s, serial #s, invoice #s, etc… you can use Excel's Data Validation feature.
 
[Click for Practice File]
Ensure Correct Length for Data Entry in Microsoft Excel 2007 2010 2013 2016 365

For example, if column B contains SKU numbers and they are required to be 10 characters in length, here's what to do...
 
1) Select the range of cells in column B;

2) From the Data tab, click Data Validation in the Data Tools group;

3) On the Data Validation dialog, slick the Settings tab and from the 'Allow:' dropdown, select 'Text Length';

4) In the Data dropdown, select 'Equal to';

5) In the Length field enter 10 (the required length of the input data);

6) Optionally, you can customize the error message that appears if someone inputs an invalid entry. Click the Error Alert tab, enter a title for the error dialog and a message to the user.
 
Data Validation Text Length Error in Microsoft Excel 2007 2010 2013 2016 365

7) Click OK.

CAUTION: Although Data Validation is a very useful feature in Excel, it does have some limitations which you need to keep in mind.
 
• If a cell from outside of the Data Validation range is copied and pasted over a cell containing Data Validation, the validations rules will be overwritten and, subsequently, any value can be entered in that cell.

• Data Validation does not apply to the results of formulas.

• If the Stop option is not selected in the Style dropdown on the Error Alert tab, any value can be entered.

2/21/17

How To Recover A Workbook You Forget To Save Changes To

How many times have you accidentally closed a workbook without saving changes and immediately realized your error. This may happen after you have made changes to an existing workbook or after you have been working on a new workbook and forget to save it.
 
Save Changes Dialog in Microsoft Excel 2007 2010 2013 2016 365
With Excel 2007 and earlier versions, situations like these would leave you completely out of luck. If your computer crashed however, there was a possibility that Excel would retrieve the last AutoSaved copy of workbooks that were open at the time of the crash and allow you to recover them from the AutoRecover pane. Though, my experience is that this was not always reliable.

Frequently saving multiple versions of your work is probably your best defense. Though I have to be honest with you, I don't always practice what I preach, as I have a very poor habit of infrequently saving my work. But that doesn't mean that you shouldn't.

Recover Unsaved and Previous Versions of a Workbook
Starting with Excel 2010, a new a new feature called 'Manage Versions' was introduced (well. not really 'introduced' because very few Excel users have even heard about it). With 'Manage Versions', you have the ability to recover unsaved files as well as open earlier versions (i.e. a few minutes ago) of a file that you are currently working on.

To ensure that your versions feature is enabled, click File, Options, Save. Make sure that the that the 'Save AutoRecover..minutes' and "Keep the last autosaved version...' settings are checked.
 
AutoRecover Settings in Microsoft Excel 2007 2010 2013 2016 365
By default, Excel automatically saves a version of the workbook you are working on every 10 minutes. I have changed mine to every 5 minutes.

Open an Autosaved Version of the Current Workbook
To open an recently autosaved version of the file you are currently working on, click File, Info and choose one of the versions listed. The autosaved workbook will open with a different name, thus allowing have two versions of the same workbook open.

You will see a message above the formula bar warning that this is not the latest version of the file (see image below). Do not click on Restore unless you want to replace the current version of your workbook with this previous autosaved version. If you DO need to work with both versions (current and previously autosaved) of a workbook, be very careful as both workbooks may look identical and there is a risk that you may accidentally overwrite some of your recent work.
 
Recover Autosaved Versions Of Current Workbook in Microsoft Excel 2007 2010 2013 2016 365

Once you save and close a workbook, all of its old autosaved versions are removed.

Opening a Previous Unsaved Version of a Workbook
If you close a workbook without saving changes, Excel deletes all versions of the file except for the last saved version and the most recent autosaved version. When you re-open the last saved version of a workbook that was last closed without saving it, you will have the option of opening that previous 'unsaved' version.

To open the previously 'unsaved' version of the current workbook, from the File menu click Info and click Manage Versions, Recover Unsaved Workbooks.

Unsaved Workbooks
To see all 'unsaved workbooks', from the File menu click OpenRecent Workbooks and then click Recover Unsaved Workbooks at the bottom of the screen.
 
Recover Unsaved Workbooks in Microsoft Excel 2007 2010 2013 2016 365


Excel even saves a draft version of new workbooks you create and work with but never save. These draft versions are kept for 4 days and after that they are deleted.

Excel User's Don't Know What They Don't Know
These recovery features can save Excel users a lot of work if they accidentally close a workbook without saving...but only if they know the option is available. Please share this tip with every Excel user you know because, chances are, they don't know that this recovery feature is hiding right under their nose.
1-10-17

Print Different Headers and Footers for Odd and Even Pages

If you print any of your Excel reports double-sided, for example for binding into booklets, you may have wondered how to have the headers or footers (e.g. page #, name, date, etc...) printed on the left side of odd pages and the right side of even pages, the way most books do.
 
Print different headers and footers on odd and even pages

If you use a left footer and print your report double sided, scanning through the page numbers when they are bound together is more difficult because the odd page footers are now on the inside of the booklet page.

Of course, you can use center headers or footers and you won't have that problem. But If you want your headers or footers to print differently on odd and even pages, there is a Page Setup option that most Excel users overlook.

1) From the Page Layout tab, click PrintTitles to open the PageSetup dialog;

2) Click the Header/Footer tab;

3) You may not have ever noticed that option, 'Different odd and even pages', in the lower section of the dialog before. Select it now;

3) Click the Custom Header or Custom Footer button and you will see that there are now two tabs at the top of the dialog; one for odd pages and one for even pages.

4) Click the Odd Page tab and insert a footer (or header) in the right section;

5) Click the Even Page tab and insert a footer (or header) in the left section;
 
Page Setup Option For Odd And Even Headers And Footers in  Microsoft Excel 2007 2010 2013 2016 365
6) Click OK, then OK to close the Page Setup dialog.

Now when you print your report, your footers (or headers) will print on the outer edge of the pages in a double sided booklet.

Left, Center, Right
Note that you are not restricted to using the left and right sections for your headers and footers. For example, for the odd pages you may want to put the report name in the left section and page numbers in the center section and for even pages put the report name in the right section and page numbers in the center section.
 
Different Section Headers For Odd And Even Pages in Microsoft  Excel 2007 2010 2013 2016 365


Take a look at some books and notice how the headers and footers are designed. You should be able to replicate any version of these in Excel.

1/3/17

Extract A Month's Name From A Date


If you have a list of dates and you want to show just the month's full name, here are couple of options.

Apply a custom number format

On the Home tab, click the small arrow on the bottom right corner of the Number group to open the Format Cells dialog. Alternatively, you can press CTRL+1 to bring up the Format Cells dialog. On the Number tab, click Custom in the Categories section and type mmmm in the Type field and click OK. This will display the full name of the month in the cell.
 
Format Date As Month Name in Microsoft Excel 2007 2010 2013 2016 365
If you want to display the abbreviated month name (e.g. Jan, Feb, etc...), type mmm in the Type field instead.

Use the TEXT function

If you need to create a separate column of the month names, the TEXT function makes this easy.

In an adjacent column, enter the formula =TEXT(B3,"mmmm") for the first date and copy it down. This will return the month's full name as a text string. If you want to convert these to fixed values, select the cells with the formulas and drag them using the right mouse button. When you drop them, a menu will pop up from which you can choose Paste Here as Values Only.
 
Extract Month Name From Date in Microsoft Excel 2007 2010 2013 2016 365


You've probably already figured out that if you want an abbreviated month name, you should use mmm in the formula. Also try other combinations of d=day, m=month and y=year (i.e. mmmm d, yyyy) in both the custom date format and the TEXT function.

11/29/16

The Format Painter Pattern Swipe


You probably already know about tha Format Painter for quickly copying formatting. Select a formatted cell or range, click Format Painter (Home tab), then select another cell or range to apply the same formatting.

Did you also know that after selecting the formatting to be copied, you can double-click the Format Painter and apply the formatting to multiple ranges by consecutively selecting each range...then click the Format Painter tool (or press Escape) to turn it off? You probably knew that too.

Well, here's something most Excel users don't know about Format Painter.

If you want to copy the formatting from a small range of cells and apply the same pattern to a larger range of cells, do you double-click the Format painter and then click multiple times... each time applying the formatting to a range the same size as the original range? That's what I see most Excel users do...and that's the way I did it for years.
 

Format Painter Copy Formatting in Microsoft Excel 2007 2010 2013 2016 365

But you don't have to do it that way. There is another faster and easier way.

Because the Format Painter recognizes patterns when you select a range of cells, you can quickly apply the same pattern across a larger range simply by dragging the Format Painter across the larger range with one swipe.

 
Click And Drag Format Painter To Copy Formats in Microsoft Excel 2007 2010 2013 2016 365

The result is......
 
Format Painter Pattern Recognition in Microsoft Excel 2007 2010 2013 2016 365


The same formatting pattern is extended across the larger selection regardless of its size and dimensions.
11/8/16

Quickly Filter Data Using The Active Cell's Value Or Color

If you need to quickly filter your table based on a cell's contents, font color or fill color, it's just a right-click away.

Now if you color code data in your cells, you can quickly filter the list to show only cells containing the same color, simply by right-clicking and selecting Filter,Filter by Selected Cell's Color.

When you're finished, you can quickly turn off the AutoFilter arrows by using the keyboard shortcut CTRL+SHIFT+L (or ALT, then A, C).
Quickly filter your data based on a cell's color by right clicking


10/4/16

Summing The Same Cell Across Multiple Sheets


Often we have workbooks where all of the sheets have an identical layout, for example, monthly reports.

Whenever you want to sum the same cell from multiple sheets here are the steps you need to take.

In the example below, we are going to put a SUM formula in cell C4 of the Summary sheet that will give total of cell D7 from the sheets Jan, Feb and Mar.

1) First, select the cell where you want to put the total;

2) On the Home tab, click the AutoSum command (or use the keyboard shortcut ALT+semicolon);

3) Next, click the tab of the first worksheet you want to sum;

4) Hold down the SHIFT key and click the last sheet tab. All of the sheets in between will be selected. (If you want to sum only specific sheets, instead of the SHIFT key, hold down the CTRL key and click each sheet tab you want to include in your SUM formula);

5) Now, select the cell that you want to sum. You need to do this only on one sheet;

6) Finally, press Enter.

The values from that one cell on all the selected sheets will be summed.
 
Sum Same Cell Across Sheets in Microsoft Excel 2007 2010 2013 2016 365

8/30/16

Resize the Filter Dialog to Display Long Text Values

When you use Excel's Filter (Data, Filter) and your list contains items that are too long to display in the dialog, choosing items to filter can be difficult.
 
Long text not visible in Autofilter Dialog in Microsoft Excel 2007 2010 2013 2016 365
The filter dialog contains a horizontal scroll bar which you can use to view the part of the text not visible on the right, however the text on the left scrolls out of view.
 
Scroll Long Text Filter Dialog in Microsoft Excel 2007 2010 2013 2016 365
You may not have realized that this dialog box can be resized. On the bottom right corner there are three small dots indicating that the dialog can be resized.
 
Resize Auto Filter Dialog in Microsoft Excel 2007 2010 2013 2016 365
Click and drag this corner to make the dialog wider and longer. That will make it easier to see more of the items in your list.
 
Resize Autofilter List Dialog in Microsoft Excel 2007 2010 2013 2016 365

Unfortunately, once you close the dialog, it reverts back to the default width, so you need to resize the filter dialog each time.

 

8/2/16

Cleaning Nasty Things From Your Worksheet

Blank Cells Aren't Always Blank
Many users new to Excel soon discover that if you press the spacebar on your keyboard, the contents of a cell seem to disappear. Too often they develop a bad habit of using this technique to "clear" the contents of cells.

The fact is, a blank space in a cell is not the same as a blank cell. Excel sees a blank space as if it was a text character such as "a" or "b".

Blank spaces can cause havoc in your worksheets.

Many formulas, functions and charts may not work or may give erroneous results if cells containing a blank space are included in its calculations.

Filtering and sorting will frustrate you and all sorts of other nasty things may result.

Bottom line: Never clear cells in a worksheet using the spacebar!

I know YOU wouldn't do this but you may be working with worksheets from other users. So if you find a spreadsheet with this issue here's a solution.

To find if a worksheet contains cells with just a single blank space, try this...

1) Press CTRL+H to bring up the 'Find and Replace' dialog;
 
Find And Replace Remove Spaces in Microsoft Excel 2007 2010 2013 2016 365
2) Click in the 'Find what' field and type a single blank space;

3) Make sure the 'Replace with' field is empty;

4) Select the 'Match entire cell contents' option. If you don't see that option, click the Options>> button;

4) Click the Replace All button. All cells containing just a single blank space will be cleared.

Other nasty things that may be hiding in your worksheets:

Remove Multiple blank spaces
Remove Multiple Blank Spaces in Microsoft Excel 2007 2010 2013 2016 365It is also possible that your worksheet contains cells with two or more blank spaces, either coming from imported data or someone has typed multiple spaces. If that's the case, follow the procedure described above but instead type 2 blank spaces in the 'Find what' field and leave the 'Match entire cell contents' option unchecked.

You may find that it's better to do this (multiple space) procedure first, then follow the above procedure (using the 'Match entire cell contents' option) to remove the cells with just a single space.

Remove 'Non-breaking Space' characters
Sometimes the above steps don't clear some blank spaces. It is likely that these cells contain 'non-breaking space' (NBSP) characters. NBSPs are characters (with a decimal value of 160) that are commonly used in web pages. When data is copied or imported from a web source, it often contains NBSPs.

Although, it's possible that some other character may be included in your data, i find that in the vast majority of cases it is the NBSP that causes the most problems.

To get rid of these NBSP characters in an Excel worksheet...

1) Press CTRL+H to bring up the 'Find and Replace' dialog;
 
Find And Replace Remove Nbsp Character Spaces in Microsoft Excel 2007  2010 2013 2016 365

2) Click in the 'Find what' field, hold down the ALT key and type 0160 using your numeric keypad (not the top row of number keys) to enter a NBSP character;

3) Make sure the 'Replace with' field is empty**;

4) Click the Replace All button. All NBSP characters will be removed.

**If you find that this procedure removes spaces between words, you may need to use a blank space in the "Replace with' field instead of leaving it blank.

TRIM, CLEAN
Additionally, there are two Excel functions that can help you clean data (not just 'blank' cells) that contains leading or trailing spaces.

The TRIM function removes all extra spaces (except NBSPs) from text except for single spaces between words e.g. =TRIM(E1).

The CLEAN function removes non printable characters from text e.g. CLEAN(E1).

You can also combine these two functions e.g. =TRIM(CLEAN(E1)).

If you use these functions, you will need to Copy and Paste Values to replace your original data.

Depending on your particular situation, you may require one of more of the above solutions and maybe in a different sequence to clean up the data on your worksheet.
7/19/16 How to Search Through Comments in Excel

00_lead_image_search_comments_excel

Comments in Excel are great for making notes about certain cells so you can keep track of your work. If you’ve added a lot of comments to your worksheets, it can get hard to remember where you put a specific note in a comment.

However, you can search through only the comments in your worksheet or workbook, with this advanced setting.

You can start the search from any cell in your worksheet. Excel will continue to cycle the search through the worksheet and search all the cells. Press Ctrl+F on your keyboard to open the Find and Replace dialog box with the Find tab active. Click “Options”.

01_clicking_options

To narrow the search to only comments, select “Comments” from the “Look in” drop-down list.

02_selecting_comments

By default, Excel will only search the current worksheet. If you want to search through all the comments on all the worksheets in your workbook, select “Workbook” from the “Within” drop-down list. Click “Find Next” to start the search.

03_clicking_find_next

The first cell with a comment attached to it that contains the search term you entered is highlighted. The comment is not automatically shown. However, you don’t have to close the Find and Replace dialog box to view or edit the comment on the selected cell. The Find and Replace dialog box is a non-modal dialog box, which means you can work on the worksheet behind it while the dialog box is still open. If your comments are hidden, move your mouse over the selected cell to view the comment.

04_comment_found

If you want to edit the comment, right-click on the cell and select “Edit Comment” from the popup menu.

05_selecting_edit_comment

The comment is shown (if it was hidden) and the cursor is placed within the comment, allowing you to edit it. When you’re finished editing the comment, click in any other cell. Your changes to the comment are saved and the comment is hidden again, if it was previously hidden.

06_editing_a_comment

To continue searching through your comments, click “Find Next” again on the Find and Replace dialog box to find the next occurrence of your search term. When you’re finished with your search, click “Close” to close the Find and Replace dialog box.

6/21/16

Sumif function

 

The SUMIF function will look for a certain criteria and if it finds it, then it will Sum up related cells.

For example:

sumif_function

Here we have a table of salesmen with forecasts and month of their forecast close.  On the right we listed the months and we want to sum up the Forecasts for each month.  So go to the first cell and insert the SUMIF function

sumif2

 

Range – This is group of cells that we want to look in.  In this example, we want to find the months in this column

Criteria – This is the cell we are looking for.  This example we use cell I4, which is the month in the right hand table.  In this example, it’s January.

Sum range – This is the column we need to look in to pull in the dollar amount.  In this example its the Forecast column, which is column D.

sumif_3

Then after you copy it down, you will get the results like above.  Then this will change as the values in the original table change.

6/14/16

Quickly Clean Up Inconsistent Phone Number Formats

Like many things in Excel, what's obvious to some Excel users may not be so obvious to others.

If you receive data that includes phone numbers with inconsistent formatting (e.g. 555-555-5555, (555)555-555, etc...), you need to know this simple tip that Andrew showed his coworkers that saved them many hours of manual cleanup every week.
 
1) Arrange all of the phone numbers in a column;

Format Numbers As Text in Microsoft Excel 2007 2010 2013 2016 3652) Since you won't likely be using phone numbers for doing calculations, I recommend changing the format of that column to Text. From the Number format dropdown on the Home tab, select Text;

3) With all of the cells containing the phone numbers selected, press CTRL+H to open the Find and Replace dialog;

4) In the 'Find what' field, enter the character (e.g. - ) that is separating the phone numbers;
Replace Phone Number  Separators in Microsoft Excel 2007 2010 2013 2016 365
5) Leave the 'Replace with' field blank;

6) Click Replace All;

7) Repeat steps 3 to 6 if there are other characters separating the phone numbers.
Consistent Phone Number Formats in Microsoft Excel 2007 2010 2013 2016 365
Now that all of the phone numbers are consistent, if you want, you can apply a uniform format using the Special phone number format. Select More Number Formats... from the Number format dropdown on the Home tab, then click the Special Category and the Phone Number Type.
 
Special Phone Number  Formats in Microsoft Excel 2007 2010 2013 2016 365

You can even create your own custom phone number format. After selecting the Phone Number format, click Custom in the Category list and modify the format code in the Type field.

6/7/16

Open the Last-Saved Version of an Open Workbook


Have you ever been working on your spreadsheet when you realize that something is wrong. You have deleted something by mistake or you have messed up some formulas.

If you're like me, you probably haven't saved your changes since you opened the workbook.

What do you do?

Do you try to figure out what you messed up and risk missing something? Or do you close the workbook without saving all those changes you've made and start all over?

Hopefully, you will do neither.

Maybe you would save your spreadsheet with a different name, then open the original workbook to compare data with the one you've been working on.

Not a bad plan. But here's an even better one!

Open Last Saved Copy Of Workbook in Microsoft Excel 2007 2010 2013 2016 365You can easily open a copy of last-saved version of the workbook you currently have open. Here's how...

1) From the File tab (or the Office Button in Excel 2007) click Open. The Open dialog box will appear;

2) Browse to the folder and select the filename of the workbook you want to open a copy of;

3) Click on the small down-arrow on the right side of the Open button;

5) From the menu that appears, click 'Open As Copy'. Excel will open a copy of the last-saved version of the workbook.

You will notice that the filename is the same except Excel adds a prefix, such as "Copy (1)" or "Copy of" to the filename.

Now that you have both files open, you can much more easily compare them see what data you have changed.

5/31/16

Filter Your Data for Unrelated Items

Beginning in Excel 2010, there were a couple of great new options added to Excel's autofilters, tables, Pivot Tables and Pivot Charts. Two of these options, 'Search' and 'Add current selection to filter" have transformed the way we filter our data. These features give you the ability to quickly and easily filter multiple unrelated items into a single listing even if you are working with large data sets.
 
Autofilter Add Current Selection To Filter in Microsoft Excel 2007 2010 2013  2016 365 For example, if your data contains a list of cities that you want to filter down to 5 specific cities, you can't do that with regular autofilter or even a Custom Filter. You could use Advanced Filter, but using the 'Search' and 'Add current selection to filter' options is often easier.

Checking off individual items in autofilter is pretty simple but for larger lists it is often faster to use the Search box rather than scrolling and looking for items in a long list. Also, for partial text matching, combining 'Search' with 'Add current selection to filter' makes this process so much faster and more versatile.

For partial matching you just begin typing in the Search box and the list is instantly narrowed down to only the items containing your search characters. If there are items in the list that you don't want to include in your filter, you can simply uncheck those items. Then click OK to apply the filter to your data.

If you want to include additional items in your existing filtered list, start typing again in the Search box and when you have narrowed down the list, before clicking the OK button, click the 'Add current selection to filter' option. Now, when you click OK, the items matching your second search will be added to the previously filtered list. You can repeat this process over and over until you have all the items you want.

You have to be very careful though to select the 'Add current selection to filter' option each time or you will have to start all over. It would have been nice and saved a lot of frustration if Microsoft had made this option 'stick' until you unchecked it.

Note that the 'Add current selection to filter' cannot be used with the 'Filter by Color' and 'Text Filters' options.
5/20/16 Copy Multiple Ranges of Data and Paste Them As One Single Range

Clipboard Group in Microsoft Excel 2007 2010 2013 2016 365When you click Copy or press CTRL+C in Excel, that data is temporarily stored in an area of your computer's memory called the clipboard.

The usual way we use the clipboard is that we Copy something and then Paste it somewhere else. Since the copied item is temporarily stored on the clipboard, we can even Paste it multiple times in multiple places, until we either Copy something else or hit the ESC key.

You may have thought that when you copy subsequent items to the clipboard, the previous copied item is replaced or that when you hit the ESC key, the clipboard is cleared. That's not what actually happens.

What you may not know, or if you're like me and forgot all about it for a few years, is that in Microsoft Office, you can copy and store multiple items on this clipboard. Those items can subsequently be pasted somewhere else, either individually or all as a group. In fact, the clipboard can hold data from up to 24 separate copy operations.

Show The Clipboard Pane in Microsoft Excel 2007 2010 2013 2016 365So "Where is this 'secret' Clipboard?", you ask.

I am using Excel 2013, so I'm not sure if earlier versions will differ.

To view the Clipboard and any items that are stored on it, you must first open it it by clicking its dialog launcher—that tiny arrow on bottom right corner of Clipboard group on the Home tab (or by quickly pressing CTRL+C twice1). The clipboard pane will appear to the left of your worksheet.

As you will see, when you copy an item, it gets added to the top of the Clipboard list and all previous items are moved down. Once there are 24 items on the clipboard, the oldest item gets dropped. When you click the Paste command on the Ribbon or press the CTRL+V keyboard shortcut, only the last item that was placed on the clipboard gets pasted.

Pressing the Esc key doesn't actually clear items off the clipboard. It merely inactivates (greys out) the Paste command on the Ribbon and the CTRL+V keyboard shortcut. Even when Paste and CTRL+V won't work, you can still paste any item from the Clipboard list by clicking on it.

In reality, you can choose to either paste all of the items stored on the clipboard as a group or choose any individual item to paste, then choose another and another and so on. This can be done in the same worksheet, in another worksheet or even in a different workbook. This gives you capabilities that you may have thought were impossible in Excel.

There are many different ways you can leverage the clipboard's capabilities but, for this tutorial, let's look at an example of something which you probably have always wished you could do.

Say you have several lists of similar data and you want combine them into one list. Maybe the lists are in different worksheets or even in different workbooks. You have a couple of options. You can go back and forth copying and pasting each list individually, which most Excel uses do, or you can copy all the lists to the Clipboard first and then paste them all at their final destination in one step.
5/3/16

Force All New Workbooks to Print 1 Page Wide

How often do you create a worksheet and find that when you go to print it, the data is just a little too wide to fit on one page? If you're like me, 90+% of the workbooks you create you want them to print only one page wide.

So I decided I wanted ALL of my new worksheets to default to 'Fit to print 1 page wide' and for those few that I didn't want to fit on one page, I could easily change the setting.

The way to make ALL of your new worksheets default to printing 1 page wide is by changing the 'Fit to...pages wide' option for your default worksheet and workbook templates.

Choose the 'Fit to 1 page wide' option

1) Open a blank workbook;

2) If the blank workbook has more than one worksheet, right-click and delete all worksheet tabs except for Sheet1. If you prefer to have more than one sheet in your new workbooks, go to File, Options, and on the General tab, choose a number for 'Include this many sheets';

3) From the Page Layout tab on the Ribbon, click the dropdown arrow for the Width option in the 'Scale to Fit' section and choose '1 page';
 
Fit To Print 1 Page Wide in Microsoft Excel 2007 2010 2013 2016 365

If there are other Page Layout settings (e.g. margins, header/footer, etc...) you would like to change as the default for all new worksheets and workbooks, you can make those changes now.

Save/Update Your Default Workbook Template

Be careful not to make any other changes to this workbook.

1) From the File tab, click Save As;

2) In the 'Save as Type' dropdown at the bottom of the dialog box, select Excel Template (*.xltx). You will notice that the active folder changes to the Templates folder. However, you want to save this default workbook template to your XLSTART folder;

3) Next, type or 'copy and paste' this path %APPDATA%\Microsoft\Excel\XLSTART into the File Name field and press Enter. This takes you to the XLSTART folder;

4) Change the suggested file name to Book.xltx and click OK. That's the default template for your new workbooks;

5) Again, click File, Save As (you should still be in the XLSTART folder);

6) Change the name to Sheet.xltx and click OK. That's the default template for all new inserted worksheets.
 
Save To Xlstart Folder in Microsoft Excel 2007  2010 2013 2016 365


Both the Book.xltx and Sheet.xltx templates will now include your personalized Page Layout settings (including Fit to 1 page wide) and will be available in all new worksheets and workbooks.
4/19/16

Make Your Custom Number Formats Available to All Workbooks


If you frequently use custom number formats, you have probably been frustrated that each time you need to use them in another workbook, you have to recreate them from scratch.

Here's a way to make your custom number formats available in all of your new workbooks.

As an example, I frequently use the CTRL+3 keyboard shortcut to quickly format dates to a format (e.g. 5-Dec-15) that makes it obvious which number is the month and which is the day. However when using dates from 2012 and prior (e.g. 12-Dec-11), it may not always be obvious which number is the year. So I prefer to use a dd-mmm-yyyy format (e.g. 12-Dec-2011).

To format cells with a custom number format that you previously created, typically you need to go to the Custom category on the Number tab in the Format Cells dialog. That requires some jumping around with your mouse.
 
Apply Custom Number Format Dialog in Microsoft Excel 2007 2010 2013 2016 365
To make your some of your number formats easier to access, I recommend creating them as Cell Styles. Then you'll have easier access to them right in the Cell Styles gallery.
 
Apply Custom Number Format Cell Style in Microsoft Excel 2007 2010 2013 2016 365
Custom number formats are available only in the workbooks they are created in, but you'll want to have your them available for all new workbooks.

Here's how to do that...

Step 1: Create a Custom Number Format Cell Style**

1) Open a blank workbook. Be careful not to make any other changes to this blank workbook that you wouldn't want showing up in all of your new workbooks. Make sure cell A1 is selected;

2) On the Home tab, click the Cell Styles dropdown arrow;
 
Click Cell Styles Menu in Microsoft Excel 2007 2010 2013 2016 365

3) Click New Cell Style...;
Add New Cell Style in Microsoft Excel 2007 2010 2013 2016 365
4) In the Style dialog, remove the checkmarks from all except Number, then click the Format... button;

5) In the Format Cells dialog, select Custom from the Categories list and type your desired number format in the Type field. You may find it helpful to select an existing format that is similar to what you want and then modify it in the Type field;
 
Define Cell Style Number Format in Microsoft Excel 2007 2010 2013 2016 365
6) Click OK to close the Format Cells dialog and OK to close the Style dialog. Your custom number format is now available in the Cell Styles gallery.
 
Apply Custom Number Format Cell Style in Microsoft Excel 2007 2010 2013 2016 365

Step 2: Save the New Cell Style to Your Default Workbook Template

Again, be careful not to make any other changes to this workbook.

1) From the File tab, click Save As;

2) In the 'Save as Type' dropdown at the bottom of the dialog box, select Excel Template (*.xltx). You will notice that the active folder changes to the Templates folder. However, we want to save this default workbook template to our XLSTART folder;

3) Next, type or 'copy and paste' this path %APPDATA%\Microsoft\Excel\XLSTART into the File Name field and press Enter. This takes you to the XLSTART folder;

4) Change the suggested file name to Book.xltx and click OK;

5) Again, click File, Save As (you should still be in the XLSTART folder);

6) Change the name to Sheet.xltx and click OK.
 
Save To Xlstart Folder in Microsoft Excel 2007  2010 2013 2016 365
BOOK.XLTX is the template that will open each time you create a new workbook. SHEET.XLTX is the template that will be used when you insert a new worksheet into a workbook. Both of these templates now include your custom number format as a Cell Style and will be available to all new worksheets and workbooks.

Now just select the cells where you want to apply the format and click the desired format in the Cell Styles gallery.
 
Apply Custom Cell Style in Microsoft Excel 2007 2010 2013 2016 365

** Please understand that I'm not saying that you should create ALL of your custom number formats as Cell Styles. I recommend doing this for some formats that you use frequently because it makes them a little easier to access from the Cell Styles gallery.

To use the usual process to create custom number formats that you can use in all new workbooks, substitute the Step 1: Create a Custom Number Format Cell Style section (above) with the following steps.
 
Create Custom Number Format Dialog in Microsoft Excel 2007 2010 2013 2016 3651) Open a blank workbook. Be careful not to make any other changes to this blank workbook that you wouldn't want showing up in all of your new workbooks. Make sure cell A1 is selected;

2) Click the Number dropdown on the Home tab;

3) Click More Number Formats...,

4) Select Custom in the Categories list;

5) Create your format in the Type field;

6) Click OK.
4/12/16

Highlight Upcoming Dates in Real Time

Highlight dates that are within 7 days of today

1) Select all of the date cells where you want to apply this formatting;

2) From the Home tab, click Conditional Formatting, then click New Rule... The New Formatting Rule dialog appears;
 
Conditional Formatting New Rule Upcoming Dates in Microsoft Excel 2007 2010 2013 2016 365

3) In the 'Select a Rule Type' section, select 'Format only cells that contain';

4) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than' from the second dropdown and type the formula =TODAY()+7 in the third field. TODAY() is an Excel function that returns the current date (in this example, the current date is April 7). So, the formula TODAY()+7 means '7 days from now'. If the Conditional Formatting rule 'Cell value less than TODAY()+7' evaluates to True, formatting will be applied to that cell;
 
Conditional Formatting New Rule Upcoming Date 7 Days in Microsoft Excel 2007 2010 2013 2016 365
5) The next step is to choose the format to apply to cells that meet this condition. To do this, click on the Format... button;

6) In the Format Cells dialog, click the Fill tab and choose a background color. If you use a dark color (e.g. red) for the background, you may prefer to select a lighter font color (e.g. white) from Font tab;

7) Click OK to close the Format Cells dialog and you will see a preview of the cell formatting you selected;
 
Format Preview Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
8) Click OK and you will be returned to the worksheet and any dates within 7 days of the current date should be highlighted.
 
First Condition Formatting Applied in Microsoft Excel 2007 2010 2013 2016 365

Next, Highlight dates that are within 14 days of today

1) With the date cells still selected, click Conditional Formatting on the Home tab, then click New Rule... The New Formatting Rule dialog appears;

2) In the 'Select a Rule Type' section select 'Format only cells that contain';

3) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than' from the second dropdown and type the formula =TODAY()+14 in the third field';

4) Click on the Format... button and choose the formatting you want to apply to cells that are within 14 days of now;

5) Click OK to close the Format Cells dialog, then click OK again and you will be returned to the worksheet and all dates within 14 days of the current date should be highlighted with a yellow background.
 
Second Condition Formatting Applied in Microsoft Excel 2007 2010 2013 2016 365
I know! I know! The dates within 7 days aren't red anymore! So let's see why?

Changing the Order of Conditional Formatting Rules

1) With the cells still selected in the worksheet, on the Home tab, click Conditional Formatting, then select Manage Rules... The Conditional Formatting Rules Manager dialog appears. You can now see the two rules we previously added ;
 
Conditional Formatting Rules Manager in Microsoft Excel 2007 2010 2013 2016 365
2) When we apply two or more rules to the same range of cells, the newer rules are always added at the top of the list in the Rules Manager. Rules are then evaluated in the order they are listed, with precedence given to the higher rules.

Since our '14 day' rule was added last, it is evaluated first. For cells where this rule =TODAY()+14 evaluates to True, the yellow backgound formatting is applied. If a second or subsequent rule also evaluates to True, the formatting for that rule will be applied as well, unless there is a conflict between the two formats. In this example, the first rule sets the cell's background color to yellow and the second rule sets the cell's background to red. Because of these conflicting formats the first rule takes precedence and only the yellow background format is applied.

Obviously, there is something wrong with this. The reason is the way we have applied these rules.

There are a couple of ways to correct this. The preferred way is to add the rules in the correct order when they are being initially set up. In this example, if we had added the '14 day' rule first and then the '7 day' rule last, everything would have been fine.

Another way to correct this is to change the order of precedence by (1) selecting the '7 day' rule in the Rules Manager dialog and moving it to the top by (2) clicking the Move Up arrow, giving it precedence.
 
Move Rule Up Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
Now the cells are formatted correctly since the '7-day' rule is being evaluated first.
 
Conditional Formatting Two Rules in Microsoft Excel 2007 2010 2013 2016 365


Projects due within 7 days are highlighted in red and projects due within 14 days are highlighted in yellow. Each passing day, these rule will be evaluated based on the current date because of the TODAY() function used in the conditional formatting rules.
3/8/16 Sort Numbers Regardless of Their Signs

Whenever you sort, in descending order, a list of numbers that includes both positive and negative amounts, the largest positive numbers go to the top and the largest negative numbers go to the bottom. On a short list, this may not be a problem, but on a long list, your largest positive and largest negative numbers will be far apart and difficult to compare.
 
Sorted Variances In Table High To Low_in_Microsoft_Excel_2007_2010_2013_2016_365
For example, if you are working with variances in a report, you may want to see all large variances grouped together, whether they are positive or negative. Also, if you work with bank or other reconciliations where you need to match up offsetting debits and credits, this technique may be able to help. Or, maybe you have other situations where you need to sort numbers but ignore their signs.

Using Excel's ABS() function to sort numbers by absolute value (i.e. the number without its sign) can help you accomplish these tasks.

The syntax of the Absolute function is =ABS(number). Number is the number or a reference to a number for which you want the absolute value.
 
DOWNLOAD PRACTICE FILE HERE

1) In a blank cell, in the column immediately to the right of your data (or you can insert a new column), enter the formula =ABS(E5) where E5 is the first cell in your variance column;

3) Copy that formula down the column to the bottom of your list;

4) Now sort your data in descending order based on this column.
 
Variances In Table Sorted High To Low By Absolute Value_in_Microsoft_Excel_2007_2010_2013_2016_365
You will now have your data sorted based on their 'absolute values' showing the largest values (positive or negative) at the top of your list.

For a bank reconciliation, this means offsetting debits and credits will be grouped together making it easier to match them up.
 
Sort And Match Offsetting Debits And Credits_in_Microsoft_Excel_2007_2010_2013_2016_365
3/1/16 The DATEDIF Function

There's a 'secret' function in Excel that makes calculating the number of years and months between two dates easy. The function is called DATEDIF (i.e. date difference) and for some reason Microsoft has essentially hidden it. You won't even find it in the functions list.
 
Datedif Not In Functions List_in_Microsoft_Excel_2007_2010_2013_2016_365
The syntax for the DATEDIF function is
=DATEDIF(start_date, end_date, "interval").

Calculate the Number of Months Between Two Dates
Let's use the same dates as above to calculate the number of complete months ("m") between the start date and the end date. The formula we use is =DATEDIF(B1,B2,"m") where B1 is the start date and B2 is the end date. The result is 2.
 
Formula To Calculate Months Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365
Calculate the Number of Years Between Two Dates
Similarly, the formula to calculate the number of complete years ("y") is =DATEDIF(B1,B2,"y"). Although using the dates in the example above would result in 0 'complete' years, if you use a start date a year or more earlier you will see the result.
 
Formula To Calculate Years Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365

Calculate the Number of Years or Months Between Today and Some Other Date
Maybe you want to calculate the number of years or months between today and some other date.

For dates in the past (e.g. birth dates, hire dates, etc...), substitute the end_date with the TODAY() function. For dates in the future (e.g. retirement date, maturity date, etc...), substitute the start_date with the TODAY() function.
 
Formula To Calculate Years From Today_in_Microsoft_Excel_2007_2010_2013_2016_365

A few things to keep in mind about the DATEDIF function
 
1) The start_date must be less than or equal to the end_date, otherwise it will give an error.

2) Acceptable interval codes are "d", "m", "y", "ym", "yd", "md" (with quotes);

3) It may appear obvious what the "ym", "yd", and "md" interval codes mean but they require a second look. The "ym" interval code returns the number of months between the two dates as if they were in the same year and ignores the year. The "yd" and "md" interval codes returns the number of days between the two dates as if they were in the same year and ignores the year.
 
Datedif Function Options_in_Microsoft_Excel_2007_2010_2013_2016_365
Calculate the Number of Years, Months and Days Between Two Dates

To calculate the number of years, months and days between two dates you will need to create a formula using multiple DATEDIF functions. This formula will return a text string.

Again, assuming your start date is in cell B1 and your end date is in cell B2 you can use the following formula to return a text string showing the number of years, months and days between those two dates.

=DATEDIF(B1,B2,"y") & " years, " & DATEDIF(B1,B2,"ym") & " months," & DATEDIF(B1,B2,"md") & " days"
To use this formula in your worksheet, Copy and Paste it from here into your Excel formula bar, then adjust the cell references.
 
Formula To Calculate Years Months Days Between Two Dates_in_Microsoft_Excel_2007_2010_2013_2016_365
As you probably have concluded, to calculate date differences based on today, you can substitute the TODAY() function for one of the date references in this formula.

 
2/23/16

Drill Down to See the Details for Any Pivot Table Subtotal

Pivot Tables are great for summarizing lots of data. But when you're looking at the subtotals or grand totals in a Pivot Table, you often need to know what makes up that amount. There is a 'so-simple-but-not-so-obvious' trick that you can use to quickly drill down and see those details.

Simply double-click on any subtotal or total in a Pivot Table to get the details of that amount.

For example, in the Pivot Table below, a breakdown of the Jan-2016, Computer Paper sales can be found by double clicking on its subtotal. A new worksheet will be inserted into your workbook containing the detailed source data that was used to calculate that subtotal.

 

Double Click To Drill Down Pivot Table Totals_in_Microsoft_Excel_2007_2010_2013_2016_365

A word of caution about working with these detail sheets: When you have finished working with your detailed sheet, be careful not to close it without saving changes. Remember that is is part of the workbook containing the Pivot Table, so you could inadvertently lose some of your unsaved changes. I have done this more times that I am willing to admit.
2/9/16

Working with Worksheet Functions Made Easier

Functions are precoded formulas that are built right into Excel, such as AVERAGE, SUM and VLOOKUP. They require only the values needed for the calculation and therefore save you the work of creating a more complex formula.
 
Excel has hundreds of functions available. If you know (or have an idea) which worksheet function you want to use, but you're not exactly sure how to use it, here are a couple of tricks that will make working with them easier.

Function AutoComplete
You start a function by typing an equal sign (=). Then, when you type the first letter or two, a list of matching functions will be shown. [Optionally, at this point, you can select the desired function form the list with your mouse or the arrow keys]. As you continue typing the function name, the list will be narrowed down to one. When there is only one match displayed, rather than finish typing the remainder of the function name, you can press the Tab key on your keyboard and Excel will to AutoComplete the name and show you each of the arguments (i.e. required parts) of the function.
 
Shortcut To Enter Function Name in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

Formula Dialog and Help
Once you have typed the function name, you can press CTRL+A to bring up the Function Arguments dialog box which will help you complete the function and give you explanations for each argument.

You can also click the 'Help on this function' link at the bottom left corner of the Function Arguments dialog for additional help on the function.
 
Keyboard Shortcut To Get Help With Functions in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

Move it
As you are entering a function, the screentip tool often gets in the way and covers one or more cells that you either want to view or want to select for one of the arguments of the function. A little known trick is that you can actually move this screentip just by pointing to it and dragging it out of the way. Almost everytime I show this to someone, they inevitibly say "Wow! This has been annoying me forever."
 
Move Function Help Tooltip Out Of The Way in  Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

I hope that these few tips will help make it easier for you to take full advantage of the hundreds of amazing functions that Excel provides.

1/5/16

How to Sort Subtotals

If you try to sort data containing subtotals, you will likely get a message that states "This removes the subtotals and sorts again". At first glance you may think that this means it's not possible to sort the data without first removing the subtotals.
 
How To Sort Subtotals in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
However, you need to carefully read the rest of the message, "If you want to sort the subtotaled groups, choose the Cancel button, then collapse the outline and try again." What this is saying is that if you want to sort data that includes subtotals, you first have to collapse the outline so that just the subtotals are visible, then sort the data, then expand the outline.

So, basically, to sort subtotaled data you must first collapse the subtotals.
 

Collapse Outline To Sort Subtotals in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

 

12/22/15

Sort by Background Color or Font Color



If you are using Excel 2007 or later, you can now sort your data based on the background color of the cells or the font color.  If you use Conditional Formatting icons, you can also sort by icon color.

To sort by color...

1) Select the data you want to sort (or click a single cell in a table);

2) From the Data tab, click the Sort command (i.e. the one with the Z-A/A-Z image);

3) In the Sort dialog, you'll find the 'sort by color' options on the Sort On dropdown.
 
Sort By Cell Font Or Icon Color in Microsoft Excel 2007_2010_2013_2016

4) When working with multiple colors, for each color, click the Add Level button on the Sort dialog to set the order you want it sorted.
 
Choose Sort Order For Colors in Microsoft Excel 2007, 2010, 2013, 2016, 365


NOTE: You can also access the sort options by right clicking a cell in your table.
 
Right_Click_Sort_By_Color_Menu_Options in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

12/15/15 How to Create Popup 'Tooltips' Using Data Validation

Maybe you already use Cell Comments in your workbooks. They are a great way to provide additional information in your worksheets that you don't want printed with your data. Just hover the mouse over the cell and the comment pops up.

Popup Cell  Comments in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365But don't you hate it when a worksheet has a lot of comments and they start popping up and getting in your way everywhere you move your mouse? ...and those annoying red triangles!?

And if you use the keyboard to move to a cell containing a comment, the comment doesn't pop up.

That's where this neat trick using Data Validation may be a better option. You can use this trick to provide information or instructions to a user about a specific cell or range of cells in a tooltip-like popup. This information will appear whether selecting the cell using a mouse or a keyboard.
 
Data Validation Input Message Popup in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

Here's how...
1) Select the range where you want the information to popup when a cell is selected;

2) From the Data tab, click the Data Validation command and choose Data Validation...;

3) Click the Input Message tab;

4) Make sure that the 'Show input message when cell is selected' option is checked;

5) Enter a Title for the message which will appear in bold at the top of the message. You can leave this blank if you want;

5) In the 'Input message' field, type the message you want users to see when they select the cell (maximum 255 characters);

7) Click OK.
Now select a cell in the range and you will see a small message box pop up. When you click away from the range, the message will disappear.

I bet that already you're thinking of new uses for this tip.

But don't get too excited. There are some limitations to what you can do with this message box. You cannot move or resize the message box. You cannot change it's formatting. And the message length is limited to 255 characters. Also, since you cannot move the message box, it could end up blocking information in other cells that you want to see.

Despite these limitations, I think, in certain situations, this may be better alternative to cell comments or text boxes.
12/8/15

How to Apply a Semi-Transparent Color to Text Boxes and Shapes


We all know how to create Text Boxes and Shapes and apply a color to them. But here's something that you may not know. There is a 'hidden' feature in Excel that allows you to apply a semi-transparent look to your Text Boxes and Shapes.

This is probably not something that you will use everyday but it is a neat trick and can be useful at times.

To apply a semi-transparent color to your Text box or Shape...

1) Select any Text Box or Shape;
2) Click the Fill tool on the Home tab in the Fonts group and select More Colors...;
3) You'll notice on the bottom of the dialog box there is a Transparency control that allows you to vary the percentage of Transparency from 0% to 100%. I find that around 50% to 80% gives a realistic transparent look.
 
Add Semi Transparent Color To Text Boxes Objects And Shapes in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

12/1/15

Quickly Insert Blank Rows Between All Rows



If you have a list of data and you need to insert a blank row between each row in the list (i.e. double-spacing), here is a cool little trick that takes just a few seconds.

1) Insert a new 'helper' column or use a blank column immediately to the right of your data;

2) In this column, enter 1 in the cell adjacent to your first row of data and in the next cell down type 2;

3) Select the cells containing 1 and 2 and double-click the Fill Handle [the small black square in the lower-right corner of a selected cell or range]. A series of numbers (1,2,3,etc...) is added from the top to the bottom of your list;

4) With that series of numbers still selected, press CTRL+C (i.e. Copy), select the first blank cell immediately below the current selection and press CTRL+V (i.e. Paste);

5) Now from the Data tab click the Sort command. In the Sort by' field, select the helper column that contains the series of numbers and click OK. The entire table will be sorted with a blank row between each data row;

6) You can now delete or clear the 'helper' column.
 
Insert A Blank Row Between Each Row Of Data Small in Microsoft Excel 2003, 2007, 2010, 2013, 365

If you need to insert just a few blank rows, you can simply select one cell in each row (e.g. B5,B6,B7,B8,B9), right-click and choose Insert, Entire Row, OK.

And if you decide that you want to remove the blank rows...I've got that covered too.

1) Select all cells in a single column containing the blank rows, press the F5 key on your keyboard, click the Special... button, select the Blanks option, and click OK. This will select all blank cells.
 
Select Blank Cells in Microsoft Excel 2003, 2007, 2010, 2013, 365 2) Now, right-click, choose Delete, Entire Row, OK.

11/17/14 Easily Copy Print Settings From One Worksheet to Another

When you make a copy of a worksheet tab in Excel by holding down the CTRL key and dragging the sheet tab, the print settings get copied as well. However, if you want to copy just the Print Settings from one sheet to one or more other sheets, there doesn't appear to be an option to do that.

But there is! In fact, there is a VERY SIMPLE , EASY and FAST way to do this that most Excel users have never heard of.

1) First, select the sheet tab whose print settings you want to copy;

2) Hold down the CTRL key and click each sheet tab you want to copy the print settings to;

3) From the Page Layout tab, click Print Titles (to open the Page Setup dialog), then simply click OK (or press ENTER).

4) Last VERY IMPORTANT step:  Right click any sheet tab and click Ungroup Sheets. If you don't do this, you could mess up all of your sheets because everything you do to one sheet will be duplicated in all other grouped sheets.
 
Copy Print Settings From One Worksheet To Another in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
11/3/15

A Thesaurus in Excel?

 

In business, nothing else comes close to Excel for analyzing, organizing and presenting numbers.

But numbers alone don't tell the whole story. Even in Excel, we need to use the right words to convey our message effectively.

When you are stuck for just the right word in a report and need to find one with a similar meaning, what do you do? Do you pull the old thesaurus off the shelf?

Maybe you have discovered that Thesaurus.com is a great website for finding synonyms.

But did you know that you can access a thesaurus from within Excel?

Yes. You can and it's easy.

The way the Thesaurus works in Excel is different depending on if the word is a single word in a cell or in a cell with multiple words.

Single word in a cell

1) Select the cell containing the word you want to replace;

2) Click the Review tab;

3) In the Proofing group on the left end of the Ribbon, click Thesaurus . A Thesaurus pane will appear on the right side of the Excel screen with a list of synonyms for the word in the selected cell;
 
Alternatively use the Keyboard Shortcut SHIFT+F7 instead of steps 2 & 3 above

4) If you don’t see the exact word you want, click a similar word and a list of it's synonyms will be shown. When you find the word you want to use, point to it and a small arrow will appear to the right of the word

5) Click the arrow and choose Insert. The word in the selected cell will be replaced with the synonym you chose;
 
Thesaurus One Word In Cell in Microsoft Excel 2003, 2007, 2010, 2013, 365


A word in a cell with multiple words

1) Click in the Formula Bar and select the word you want to replace (or double-click the cell and select the word within the cell on the worksheet that you want to replace);
 
Highlight Word In Cell in Microsoft Excel 2003, 2007, 2010, 2013, 365

2) Click the Review tab;

3) In the Proofing group on the left end of the Ribbon, click Thesaurus . A Thesaurus pane will appear on the right side of the Excel screen with a list of synonyms for the word you selected;
 
Alternatively use the Keyboard Shortcut SHIFT+F7 instead of steps 2 & 3 above

4) If you don’t see the exact word you want in the list, click a similar word and a list of it's synonyms will be shown;

5) Because you are in 'edit mode', you cannot click or select anything in the Thesaurus pane. However, if you see a word you want to use, at this point you can just begin typing and it will replace the word you have highlighted in the cell. If you don't see the exact word you want to use, press the ESC key to exit 'edit mode', then click a similar word in the list to get a list of synonyms for it.

6) To use a word, click the arrow to the right of it and choose Copy;

7) Now, highlight the word you want to replace in the Formula Bar (or double-click the cell and select the word within the cell on the worksheet that you want to replace);

8) Right-click and Paste.
 
Find Word Synonym In Thesaurus in Microsoft Excel 2003, 2007, 2010, 2013, 365
10/20/15

Easily Add Text To Excel Shapes

I love using Shapes in Excel. They are great for creating user guides and tutorials, and also great for drawing attention to important information in your worksheets.
 
Use Shapes To Draw Attention in Microsoft Excel 2003, 2007, 2010, 2013, 365

Over the years, many people have asked me how to they can create shapes like mine. I sometimes see people using shapes and then overlaying them with text boxes. But there's no need to use both shapes and text boxes.

When you insert a Text Box, you'll notice that you are automatically put into Edit Mode with a blinking cursor ready for you to start typing some text. However, when you insert a Shape, such as a rectangle, a block arrow, a flowchart symbol and most other shapes, you do not go into Edit Mode. Even when you click in the Shape, there's no blinking cursor...nothing happens. It appears that you cannot enter text in these Shapes.
 
How To Enter Text In An Autoshape in Microsoft Excel 2003, 2007, 2010, 2013, 365 

But if you right-click on most Shapes, you'll see an Edit Text option on the menu. That will allow you to add text. However, an even easier option is to simply just start typing after you have drawn or selected the Shape.
 
This Is How You Enter Text In An Auto Shape in Microsoft Excel 2003, 2007, 2010, 2013, 365

Once you insert a Shape, a new Drawing Tools/Format tab appears on the Ribbon.
 
Design Cool Stuff With Drawing Tools Tab in Microsoft Excel 2003, 2007, 2010, 2013, 365

On this tab, in the Word Art Styles group, you can find some some cool commands for formatting your text. In the Shape Styles group you'll find commands to 'pimp your shapes'. Play around a little in here. Some of the effects you can create here will make you look like a pro. You can also use the Font commands on the Home tab for formatting the text in your Shapes.
 

Create Text And Cool Graphics in Microsoft Excel 2003, 2007, 2010, 2013, 365
10/6/15

A Neat Trick To Subtotal Dates By Month/Year (Ignoring the Day)

There may be times when you want to subtotal your data by month and year, however simply Subtotaling a column of dates won't work because that will create a subtotal for each day.
 
Microsoft Excel Trick: Subtotal dates by month
Here's a trick you can use to create subtotals for each month while ignoring the day...

1) Sorted your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;

2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown (or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015);


Subtotal Dates By Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365
3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day;
 
Dates Subtotaled By Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365

4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year.
 

Display Subtotals For Month And Year in Microsoft Excel 2003, 2007, 2010, 2013, 365

9/15/15

Draw Shapes and Text Boxes to Fit Within Cell Borders

When using multiple Shapes, Text Boxes or other objects in a worksheet, you will often want to them to be the same size or line up along one side. In Excel, cell gridlines make a nice guide for doing this but do you know there is an easy way to do this that takes away all the guesswork?

Using this trick, you can easily 'snap to fit' your shape to the cell borders as you are drawing or resizing it.

Here's how...

Eis Alt Drag Size Objects To Cell Borders in Microsoft Excel 2003, 2007, 2010, 2013, 3651) From the Insert tab, click Shapes and choose an object you want to draw;

2) Hold down the ALT key;

3) Point to the top left corner of the cell where you want to begin drawing the object and press the left mouse button;

4) Drag to the bottom-right cell;

5) Release the mouse button;

6) Release the ALT key.

 

Once you have drawn the object, you can resize it by first selecting the object and then dragging one of its 8 sizing handles as you hold down the ALT key

9/8/15

 Transforming the Case (Uppercase, Lowercase) of the Text

There is a small function that we can use to transform the case of our text, the function is quite easy to use, all you need to do for this is that you need to type ‘UPPER(text/cell)’ for upper case, ‘Lower(text/cell)’ for lower case and finally ‘Proper(text/cell)’ for making the first letter of the word capital. Its usage can be seen in the images below, with cells showing Upper, Lower and proper usage along with the final output that they achieve from it.

6. upper Case

UPPER Case

6. lower Case

lower Case

6. proper see

Proper

8/17/15
Add a Line to Excel Charts

Charts in Excel offer a graphic way to “see” data, but sometimes adding an extra element can add meaning to the information. This weeks Software Tips and Tricks shows how to add a horizontal line of values to a bar chart. This can help illustrate relative values on the chart.

 

Here’s How

1.      Create or open an Excel spreadsheet. When creating the data for your chart, include a column for a static, unchanging value. This will let you add a solid line in the chart as a reference point for comparison. For example, if your Excel spreadsheet reports on several months’ data, you may decide to include a column where each cell contains the average for the period. (You can hide the column in the spreadsheet, which we will cover next week.) In this sample, Column F is formatted as a number with no decimals and displaying an Average of all chart values, using this formula:  =AVERAGE(B2:E5)

2.      After completing the chart, right-click on one the static value’s data points; in this sample, we clicked on the purple bar representing Average.

3.      In the pop-up, click Chart Type…

4.      Select Line.

5.      Click OK.

 

7/21/15
Create Custom Default Worksheet And Workbook Templates

Are you constantly having to change the settings for your worksheets, such as margins, font size, page headers, etc... every time you create a new workbook or insert an new worksheet?

I'm going to show you how to create a template containing your own personalized settings that will be applied to all new workbooks and worksheets.

When you open a new workbook, Excel uses a workbook template with default settings such as three worksheets, column widths of 8.43, no headers and footers, default print margins, etc... When you insert a sheet into a workbook, Excel uses a worksheet template with similar default settings.

Here's how to create your own personalized templates so that every new workbook and worksheet will already have the settings you want.

1) Open a blank workbook;

3) Delete all sheet tabs except for one;

4) If you want to change the default formatting of your worksheet cells, such as the font face, font size, number formats, etc..., on the Home tab, click the Cell Styles command in the Styles group. Then right click the Normal style and choose Modify. Click the Format button and make any changes you like to the default style and click OK;

5) Next, to select your preferred print settings, click the Page Layout tab. Make all the changes you want your default worksheets to have (i.e. margins, headers/footers, etc...);
 


Personalizing Your Default Worksheets?

• Want a Custom Header or Footer to appear on every new worksheet? On the Page Layout tab, click the Dialog Launcher (small arrow on bottom right corner of 'Page Setup' group) and click the Header/Footer tab.
• Want all new sheets to fit your data one page wide? From the Page Layout tab, select 1 Page in the Width field of the 'Scale to Fit' group.
• Don't like the current print margins? On the Page Layout tab, click the Margins command in the 'Page Setup' group, then click Custom Margins...
• Would you prefer the default orientation of all new sheets to be Landscape? On the Page Layout tab, click the Orientation command in the Page Setup group.
• Don't like gridlines displayed in your worksheets? Click the View tab and uncheck Gridlines in the Show group.



6) When you have selected all of the custom settings for your default worksheets, click cell A1 to make it the active cell for all new worksheets;. 7) Next, you need to save this custom worksheet as a template in your XLSTART folder. Click the File tab and Save As. In the Save as Type dropdown on the bottom of the dialog box, select Excel Template (*.xltx) and browse to the XLSTART folder.
 

Locating Your XLSTART folder

In Windows 7 and 8 you should be able to find your XLSTART folder at...
C:\Users\username\AppData\Roaming\Microsoft\Excel\

If you do not see the AppData folder, try typing the full path (above) into the Address Bar in Windows Explorer.
 
Easily locate your XLSTART directory / folder
 
Another option for quickly locating your XLSTART folder is to type or paste the following command into the Windows Explorer Address Bar or even into the 'File Name' field of Excel's Save As or Open dialog boxes and press Enter.
 
%APPDATA%\Microsoft\Excel\XLSTART

Easily locate your XLSTART directory / folder

Change the suggested name to SHEET.XLTX. This will now be the default sheet template used when you Insert a worksheet into a workbook;
 
Create a custom default workbook

9) Now you need to save this same personalized worksheet as your default 'workbook' template by saving it with a different file name. To do this, simply repeat Step 7 but this time save the workbook with the filename BOOK.XLTX;

Personally, I like my default workbook to have only one sheet rather than the default three sheets Excel offers, as I find the extra two sheets redundant. If you choose to go with a one sheet default workbook you can quickly insert a new sheet by clicking the Insert Worksheet command (immediately to the right of the sheet tabs). However, in workbooks with a lot of sheet tabs, the Insert Worksheet command may not be visible, so I recommend adding the Insert Worksheet command to your Quick Access Toolbar (QAT). Alternatively you can use Shift+F11 to insert a new sheet.

Add the Insert Worksheet and New workbook commands to your QAT

1) Right-click the QAT and choose Customize Quick Access Toolbar;

2) In the Choose commands from dropdown, select All Commands;

3) Scroll down and select the Insert Worksheet command and click the Add>> button;

4) Scroll down again and select the New Workbook command and click Add>> button. Note that the name in the list for this command is New, not New Workbook.
 
Add the Insert Worksheet command on the QAT

Now, whenever you open a new workbook or insert a new worksheet it will always contain your preferred settings.

If you chose to have only one sheet in each new workbook, you can easily insert additional sheets whenever needed by clicking on the Insert Worksheet button.

Remember that if you ever get a new computer or work on multiple computers, you can copy these two files (SHEET.XLTX AND BOOK.XLTX) so you won't have to create them again.
7/7/15

Relative cell references

The dollar sign ($) in a formula - Fixing cell references

When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behavior is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.

http://2.bp.blogspot.com/-rxLR12Fjrko/TdZMhUJQgoI/AAAAAAAAAA4/DFLrH11SOE4/s400/Dollarsign1.jpg

The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:

http://1.bp.blogspot.com/-dykAsCViB30/TdZP_7wSehI/AAAAAAAAAA8/k5FL1gpcHh8/s400/Dollarsign2.jpg

Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

6/16/15

How to Remove a Background Color From A Logo or Image

Do you have a logo or image that you want to add to your worksheets? Maybe it would look better without that background color?

Removing a background color from an image is not something you would typically associate with a spreadsheet program but, surprisingly, Excel has treasure chest full of amazing graphics capabilities**. Making an image's background transparent is easy for Excel.

Make Image Background Transparent in Microsoft Excel 2003, 2007, 2010, 2013, 365
 
This works only if the color that you want to make transparent is a solid color.
 
1) Select the logo or picture;

2) Click Picture Tools-Format tab;

3) Click Color;

4) Select Set Transparent Color;

5) Point and click the (solid) background color in the picture and magically, the background color disappears.
Steps To Make Image Background Transparent in Microsoft Excel 2003, 2007, 2010, 2013, 365
If this is something that you will be using regularly, you can make this process even easier by right-clicking on the 'Set Transparent Color' option and clicking 'Add to Quick Access Toolbar'.
 
Add Set Transparent Color Command To Quick Access Toolbar in Microsoft Excel 2003, 2007, 2010, 2013, 365


If the background color of your logo or image isn't a solid color, only some of the background will be made transparent. There's no easy way to fix this other than to find another image with a solid background color.

If there are other parts of the image that contain the same color as the background, those colors will also be made transparent. A workaround that I use for this situation is to copy the image to Paint and use the Bucket tool to fill the background with a unique color, then copy the image back to Excel and use the Set Transparent Color option.
5/12/15

Create Your Own Custom Sort Order

Sorting is an integral part of data analysis in Excel. Normally you sort text in ascending or descending alphabetical order and you sort numbers from highest to lowest or lowest to highest. This allows you to better understand relationships in your data.

But have you ever wanted to sort your data by some other order that wasn't in alphabetical or numeric order? Maybe your company wants to present its data by region. For example, in Canada, a company may want to present its data by province and territory (west to east NOT in alphabetical order). You could sort the data in alphabetical order and then move the rows of data into the your custom order...but there is a much easier way.

In Excel you can create a Custom List and use that list to sort your data in your own custom order.

First you need to create your own custom list:

1) In a blank range, enter the values of your custom list in the order that you want from top to bottom;

2) Next, select the cells you just typed;
 
Create your own custom list for sorting and autofilling

3) From the File tab, click Options, then the Advanced tab, scroll down to the General section and click the 'Edit Custom Lists...' button. (In Excel 2007, click the Office Button, Excel Options, on the Popular tab and click the 'Edit Custom Lists...'. button);
 
Create your own custom list for sorting and autofilling

 5) In the Custom Lists dialog box, click Import, and then click OK twice.
 
Import your own custom list for sorting and autofilling
Notes: There were a couple of issues in versions of Excel prior to 2013 which limited the length of a Custom List to 255 characters and items in a list could not begin with a number. I am currently using Excel 2013 and I am not experiencing either of those limitations.

Sort your data based on a custom list:

1) Select the range of cells you want to sort, or select a single cell if you want to sort the entire range;

2) From the Data tab, in the Sort &Filter group, click Sort;

3) In the Sort dialog, select the Column you want to Sort on from the first dropdown and in the Order dropdown select Custom List...

4) In the Custom Lists dialog, select your custom list and click OK. and OK
 
Sort your data in Excel based on your oen custom order

Autofill Using a Custom List

Note that Custom Lists are not just for sorting. These lists can also be used for AutoFilling cells. Type a value from your custom list into any cell and drag the Fill Handle to fill in all other value from the list. You can even AutoFill your custom list up, down, left or right. How cool is that?

Autofill a Custom List
5/5/15 Show Plus Sign On Positive Numbers

Sometime you may be asked to show numbers (e.g. increases and decreases) with plus and minus signs indicating an increase or a decrease.
Show_Plus_Signs_Displayed_For_Positive_Numbers in Microsoft Excel 2003, 2007, 2010, 2013, 365
Minus signs are easy. They automatically show up on negative numbers. But a plus sign (+) before a positive number doesn't happen automatically. It requires a little manipulation of the number formats.

Here's how...

1) Select the cells you want to apply a format to;

2) On the Home tab, go to the Number group and select select More Number Formats from the dropdown to open the Format Cells dialog. TIP: The keyboard shortcut to open the Format Cells dialog is CTRL+1;

3) On the Number tab select Custom in the Category field;

4) If there is a number format already assigned to the selected cells, the number format code will be displayed in the Type field.

However, if the cells you have selected do not have the same number format for all cells, the Type field will usually display the General format. If that's the case, select a format from the list of formats below that is closest to the format you require.

Look at the number format code in the Type field. The code to the left of the first semi-colon is for positive numbers and the code to the right of the first semi-colon is for negative numbers. Typically you will see a minus sign in the code for negative numbers, however, positive numbers don't usually show a plus (+) symbol.

To display a plus symbol for positive numbers, you can modify the format you selected simply by typing a plus symbol (+) to the left of the code for positive numbers.
 
Create_Custom_Number_Format_To_Show_Plus_Signs_For_Positive_Numbers in Microsoft Excel 2003, 2007, 2010, 2013, 365
4/28/15

Prevent Input Values That Are NOT Rounded to 2 Decimal Places

Someone asked me this week if there is a way to prevent someone from inputing values that are NOT rounded to two decimal places.

If you have been using Excel for any amount of time, you have probably come across a situation where values in your worksheet that don't appear to add up.

Displayed Value Rounding Error in Microsoft Excel 2003, 2007, 2010, 2013, 365An example is 33.33 + 33.33 + 33.33 = 100.00. On the surface, it is obvious that these numbers don't add up to 100.00. However, as you probably know, what you see on the surface (of your worksheet) is not necessarily what is really going on underneath.

Since Excel allows you to apply various number formats to your data, the displayed values may not necessarily be identical to the actual numbers in the cells. The value 33.333333 formated with 2 decimal places would be displayed as 33.33. As you can see, this has the potential to cause rounding errors on your reports.

If the numbers you are reporting are 2 decimals, you may want to prevent users from entering values that are NOT rounded to 2 decimal places.

One option is using Data Validation

1) Select the cells that you want to restrict to values rounded to 2 deciimal places;

2) From the Data tab click Data Validation, Data Validation;

3) On the Settings tab select Custom from the Allow dropdown;

4) In the Formula box enter the following formula. Replace B5 in this example with the cell address of your 'active cell' (i.e. the address shown in the 'Name Box' above column A).
 
=D5=INT(D5*100)/100
Restrict data input to 2 decimal places

5) Optionally, on the Error Alert tab, you can include a message that explains what input is expected and/or instructions to help users correct imput errors.
 
Restrict data input to 2 decimal places error message
You can allow users to enter invalid data but warn them when they type it in the cell by selecting Warning rather than Stop on the Error Alert tab.
 
Data Validation Error Alert Warning Message in Microsoft Excel 2003, 2007, 2010, 2013, 365


AN IMPORTANT POINT to understand about Data Validation is that, as good as this feature is, it does have one CRITICAL flaw. If a user copies data from another range and pastes it into a cell containing a Data Validation rule, the rule could be deleted from that cell.

I don't know why Microsoft didn't include a Data Validation setting that would prevent users from pasting and overwriting cells containing Data Validation rules...similar to the way you cannot overwrite part of an array.

There have been macros written that attempt to address this situation but they too have serious drawbacks and limitations.
3/9/15

Save a Worksheet or Even Just a Range of Cells as a PDF Document

If you don't already know, Excel has a really awesome feature that allows you to save your workbook to a PDF document. This does not affect the original workbook. It just saves a copy of the workbook in PDF format.
 
Saved Worksheet As Pdf in Microsoft Excel 2003, 2007, 2010, 2013, 365
Because PDF documents are so ubiquitous these days, sharing an Excel spreadsheet as a PDF document has its advantages. The two main ones are:

1) The receiver doesn't need to know how to use Excel or have it installed and very likely does have software that can open PDF documents.

2)  If the end user doesn't need to edit the spreadsheet, sending it in PDF format prevents accidental changes.

One of my favorite things about saving Excel spreadsheets as PDFs is that, I think the PDF often looks better than the original document.

When saving a spreadsheet as a PDF, Excel provides you with options to save the entire workbook, specific sheets or just a selection. Depending on your version of Excel, the way you do this may be a little different.
 
Save A Worksheet Selection Or Range As A Pdf Document in Microsoft Excel 2003, 2007, 2010, 2013, 365
I'm using Excel 2013, so in the Save As dialog you will find the 'PDF (*.pdf)' option by clicking on the 'Save as type' dropdown. By default, Excel will save the entire workbook as a PDF if you click Save after selecting the 'PDF (*.pdf)' option. Each printed page of your workbook will produce a page in the PDF. Note that 'Entire workbook' means the ranges that are defined in the Print Area on each sheet.

If you don't want to save the entire workbook as a PDF, you can click on the Options... button at the bottom of the Save As dialog for more choices. Here you can save: (1) a single worksheet; (2) a selection of worksheets; (3) a specific range of pages; (4) or even just a selected range of cells. The Table option will be available if you have selected all or part of a Table in your workbook.

A Specific Range of Cells
To save a specific range of cells, you need to select those cells before clicking File, Save As, PDF. Then in the Save As, Options... dialog, choose the Selection option and click OK. Then click Save.
 
Save A Worksheet Range As PDF in Microsoft Excel 2003, 2007, 2010, 2013, 365
The Print Titles (i.e. rows to repeat at top) that you have defined in your worksheet's Page Layout will be saved at the top of each page in your PDF.

Excel 2007
Note that if you are using Excel 2007, when you click the Office Button and point to Save As, you will see a PDF or XPS option. Selecting this option will open the 'Publish as PDF or XPS' dialog. If 'PDF (*pdf)' option isn't selected in the 'Save as type' dropdown, you will need to select it. When you click on the Options... button, you will see that the options are very similar to the (Excel 2013) ones pictured above. When you have selected your options, click Publish to save the PDF.
 
Save Excel 2007 Worksheet As Pdf in Microsoft Excel 2003, 2007, 2010, 2013, 365
 


3/2/15

How To Create A Hyperlink To Another Workbook

Anyone who uses the Internet knows the benefits of hyperlinks.

Hyperlinks on a web page are the text or graphics that you click on to give you instant access to websites, documents or other information no matter where they are located.

Did you know that you can also create hyperlinks in Excel? Yes. You can create hyperlinks in your Excel worksheets that will allow you to instantly open other workbooks with just one click of your mouse.

Hyperlinks are magic for Excel users. Having hyperlinks in your worksheet that allow you to quickly and easily open other related workbooks, can save you a lot of time.

You don't have to browse to the folder where the workbook is located.

Heck, you don't even have to remember where the workbook is located.

Create Hyperlink in Microsoft Excel 2003, 2007, 2010, 2013, 365

Here's how you can create a hyperlink in an Excel worksheet that will open another Excel workbook . You can even have it open the workbook at a specific page or cell/range.

1) Select a cell where you want to place the hyperlink;

2) Type the text you want to display as your hyperlink (e.g. Click Here) or you can add a hyperlink to a cell that already has some text;

3) Right click on that cell and choose Hyperlink...(or press CTRL+K);

Create Hyperlink in Microsoft Excel 2003, 2007, 2010, 2013, 365

4) Click 'Existing File or Web Page' on the left;

5)  Browse to the folder location and select the workbook you want to link to;

6) If you want the link to a specific sheet or cell/range in the workbook, click on theBookmark... button, select the sheet name (change the A1 cell reference if desired) and click OK;

Create Hyperlink To Another Workbook Sheet in Microsoft Excel 2003, 2007, 2010, 2013, 365

7) If you add a hyperlink to a blank cell, the entire path of the linked location is displayed by default. If you need to edit the 'Text to display' value, you can do that now;

Edit Text To Display For Hyperlink in Microsoft Excel 2003, 2007, 2010, 2013, 365

7) (Optional) To add a descriptive tool tip that will pop up when a user holds the mouse over the hyperlink, click the ScreenTip... button and and enter some text and click OK;

Add Tool Tip To Hyperlink in Microsoft Excel 2003, 2007, 2010, 2013, 365

9) Click OK to close the Insert Hyperlink dialog box and then save your workbook.

10) Now, try out your new hyperlink to see how it works.

Hyperlinks are very convenient for giving Excel users quick access to related information whether they are places in the same workbook, other Excel workbooks, or other documents (.doc, .pdf, .ppt, etc...). 
2/16/15

Use Excel to CONVERT From One Measurement to Another

Most Excel users I talk to don't know this but, beginning with Excel 2007, a CONVERT function was added that lets you convert a value from one measurement system to another.

At this moment, where I live, on the east coast of Newfoundland, Canada, we are in the middle of a major winter snow storm. As of 10 p.m., almost 50 centimeters of snow had fallen.

How much is that in inches you ask?

Let's use Excel's CONVERT function to find out...
=CONVERT(50,"cm","in")

Answer = 19.7 inches

The syntax of the function is...
CONVERT(number, from_unit, to_unit)

number - is the value you want to convert.

from_unit - is the units you're converting FROM.

to_unit - is the units you're converting TO



That's pretty easy to understand right?

You just need to know the correct units to use in the function.

Here are some of the more common ones...
Excel Convert Function Common Measurements in Microsoft Excel 2003, 2007, 2010, 2013, 365

11/15/2014

Zoom To Make Your Spreadsheet Fit Your Screen

You put a lot of preparation into designing your spreadsheets exactly the way you want them. But often you'll find that your spreadsheet is just a little wider than will fit your screen. Maybe you have many spreadsheets like this now where the last column or two are just out of view on the right side of your screen.
Keyboard shortcut to apply a current number format in Microsoft Excel
The problem here is that not only do you have to scroll up and down to navigate your spreadsheet but you also have to scroll left and right.

When the data is slightly wider than will display on the screen I've seen people try using smaller font sizes, make columns narrower, or abbreviate data in some columns just to make it fit. There is a much easier way.

Here it is...

1) Select a range of cells from the left-most cell (usually column A) to the right-most cell in your sheet. It doesn't matter how many rows. One or two will do;

2) From the View tab, in the Zoom group, click Zoom to Selection. Excel will automatically adjust the zoom percentage so that the cells you have selected will fit into the visible area of your screen.
Keyboard shortcut to apply a current number format in Microsoft Excel
If you want to see the current zoom percentage, you click the Zoom command.
Zoom to fit your excel worksheet to the width of your monitor

Another way to make your data fit your screen is to drag the Zoom slider on the bottom right of your Excel window. But you may find that it's difficult to get precise zoom percentages using this method.
Quickly zoom in or out of your worksheet by dragging the Zoom Slider in Excel 2007, 2010, 2013
If your mouse has a scroll wheel, holding down the CTRL key while turning the wheel will allow you to quickly zoom in and out. However, this method allows you to scroll only in increments of 10%.
Quickly zoom in or out of your worksheet using your mouse's scroll wheel in Excel 2007, 2010, 2013

9/30/14 Saving A Workbook, Worksheet Or Worksheet Range As PDF

Since nearly everyone who has a computer can read PDF files, it is often convenient to send reports created in Excel as a PDF file when a recipient just needs to see the information.

Prior to Excel 2007, you needed a third-party solution to convert your Excel workbook to a PDF document. However, since Excel 2007, saving an Excel workbook, worksheet or even a worksheet range as a PDF document has become much easier with the inclusion of PDF as one of the 'Save as type' options right in the standard Save As dialog box.

Just following these easy steps will have you saving your Excel documents as a standalone PDF files in no time.
1) The first thing you need to do before saving your Excel document as a PDF is to do a Print Preview of your document. The formatting and layout of the PDF file is determined by the print settings for your Excel worksheet, so if everything looks good there (see Options below), you're ready to save it;

2) From the File menu choose Save As;

3) In the 'Save As' dialog, click the 'Save as type' dropdown and scroll down to the bottom of the list and select the 'PDF (*.pdf)' option. You will see some additional options appear at the bottom of the 'Save As' dialog. Note that saving an Excel document as a PDF file does not affect your workbook. A separate PDF document is created independent of your workbook;
Save Excel workbook, worksheet, range or selection as PDF
4) In most cases, it is a good idea to select the 'Open file after publishing' option so that the PDF opens immediately after saving so that you can immediately see the resulting PDF file and ensure everything looks OK;

5) Now, click Save and your Excel document is converted to PDF and immediately opens in your PDF program.

Options:

By default, only the active sheet(s) is saved to the PDF. However, if you want to save the entire workbook, specific worksheets, a single worksheet, specific pages on a worksheet,  or just a selection of cells, click the Options... button in the Save As dialog.
Save Excel workbook, worksheet, range or selection as PDF
9/8/14

Formulas To Extract First And Last Names And Middle Initial

Formulas to extract last name, first name, and middle initial

Extracting the last name

Pulling the last name out of a full name is pretty easy. In my list, the last name is always followed by a comma and a space. So I just need to tell Excel to give me everything before the comma. To do that I will use the LEFT function and the FIND function in my formula.

I can use the FIND function to return the position of the comma.

With the full name (Owens, John) in cell A2, I use =FIND(",",A2) to return the position (6) of the comma. Knowing the position of the comma, I can now use the LEFT text function to return characters from the left of the full name. Since I don't want to include the comma, I must subtract 1 from the value returned by the FIND function to give me just the 5 leftmost characters.

I used =FIND(",",A2)-1 to return 5 (i.e. the length of the last name in cell A2)

The LEFT function is pretty straightforward. =LEFT(A2,5) tells Excel to return the 5 leftmost characters of the value in cell A2 (e.g. Owen).

To create a dynamic formula that will work for all of the records in my file I combined both the LEFT and FIND functions into one formula. I basically substituted the position value (e.g. 5) in the LEFT function with the FIND function.

The final formula is =LEFT(A2,FIND(",",A2)-1) and the last name result from cell A2 is Owens.

Excel formula to extract last name

Extracting the first name:

Extracting the first name can be problematic depending on the format of the data you are working with.

If the data doesn't include middle names or initials, extracting the first name is similar to how we extracted the last name above.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

With this formula we find the length of the text in cell A2 using LEN(A2) and subtract the position of the SPACE character using FIND(" ",A2). This gives us the length of the last name. We then use the RIGHT function to ask Excel to return that number characters from the right side of the text in A2.

=RIGHT("Owens, John",11-7)
=RIGHT("Owens, John",4)
"John"

If the full name includes a middle name or initial following the first name (e.g. Aitken, Shirley M.), the formula for extracting the first name gets a little (actually, a lot) more complex.

=TRIM(IF(ISERROR(FIND(",",A3,1)),A3,MID(A3,FIND(",",A3,1)+1,IF(ISERROR(FIND(" ",A3,FIND(",",A3,1)+2)),LEN(A3),FIND(" ",A3,FIND(",",A3,1)+2))-FIND(",",A3,1))))

Excel formula to extract first name

Extracting the middle name or initial:

The following formula will extract a middle name or initial by returning the text following the second space. If there is no second space, nothing is returned.

=TRIM(RIGHT(A4,LEN(A4)-IF(ISERROR(FIND(" ",A4,FIND(" ",A4,FIND(",",A4,1)+2))) ,LEN(A4),FIND(" ",A4,FIND(" ",A4,FIND(",",A4,1)+2))-1)))

Excel formula to extract middle name
8/26/14

Determine The First Or Last Day Of Week For Any Date

Sometimes you may need to determine what the first day of a week is by looking at some other date.

If we have August 20, 2014 in cell A2, we can use this formula to determine that the first day of the same week is August 17, 2014 (assuming you want to treat Sunday as the first day of the week).
=A2-WEEKDAY(A2,1)+1

Use this formula to determine first day of the week for a givendate in MicrosoftExcel
As you may know, when you type a date in Excel, it is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, when you type August 20, 2014 into your spreadsheet, Excel converts that date into the serial number 41871, even though it may be actually displayed on your worksheet as a date format. Change the number format of any date to General and you will see its serial equivalent.

When a formula is referencing August 20, 2014 in cell A2, Excel sees this date as 41871.

The second part of this formula uses the WEEKDAY function .
WEEKDAY(serial_number,[return_type])

The WEEKDAY function takes two arguments. The first option is serial_number which can be a date or a reference to a date. The second argument [return_type] is optional and therefore can be omitted. In it's basic form, the WEEKDAY function takes a date returns a number that represents the day of the week (e.g. Sunday=1, Monday=2, etc...). If you want to have Excel treat Monday as the first day of the week, you will need to use 2 for the return_type. A return_type equal to 2 uses Monday=1, Tuesday=2, etc...

Understanding the above information, we can now see how this formula woks.

1) First it takes the referenced date in cell A2 and converts it to its serial number equivalent (41871);

2) The WEEKDAY function takes the referenced date (Wednesday, August 20, 2014) and determines its day of the week. In this case, it is the fourth day of the week (using the default assumption of Sunday being the first day of the week).

3) When the weekday value (4) is subtracted from the referenced date (41871), the result is the day prior to the first day of the week, August 16, 2014 (41867).

4) Adding 1 returns the first day of the week August 17, 2014 (41868). It's a little tricky but it works.

Determine Date for First Day of a Work Week (assuming Monday):

If you are dealing with workdays, with Monday being the first workday, you can modify this formula to return Monday, August 18, 2014 as the first workday of the week that contains August 20, 2014, by using 3 as the return_type in the WEEKDAY function.
=A2-WEEKDAY(A2,2)+1

Use this formula to determine first day of a work week for agiven date in MicrosoftExcel
Determine Date for Last Day of a Work Week (assuming Friday):

Assuming Friday is the last day of your work week, you can use this formula to return the date of the last day of the work week for any given date.
Use this formula to determine last day of a work week for agiven date in MicrosoftExcel

8/19/14

Quickly Email An Excel Workbook As PDF Attachment

Now that you know how to save your Excel documents as PDF files, here's an even easier process to use when you need to (1) save your workbook as a PDF and (2) send it by email as an attachment.

Right there on your File menu, there is a Save & Send option which allows you to do this in two easy steps without having to browse from your email program to find the PDF file to attach.

As before, Print Preview your document to ensure everything looks OK. Then from the File menu select Save & Send.
The fastest way to save an Excel workbook as a PDF and send as email attachment



1) In the Save & Send dialog, there is a 'Send as PDF' option that will instantly save the current workbook as a PDF and open it in your email program attached to a new email.

2) Now you just enter the recipients, type your message, subject and click Send.

It doesn't get much easier than that.
8/11/14

Quickly Clear Formula Errors From Blank Rows

It is common in Excel reports to have blocks of data separated by blank rows. When you need to fill a formula, such as a division formula, down a column you often get errors in the blank rows.
A quick trick to clear formula errors in blank rows
If your report is small, you can simply select each cell with an error and delete the formula. However, if you have a large report, this method can be quite time consuming.

Here's a solution to help you clear all of these formula errors quickly.

1) Select the range of cells that includes all of the errors;

2) From the Home tab select Find & Select, Go To..., Special... or press the F5 key on your keyboard;

3) In the Go To dialog, select the Formulas option, clear all checkmarks except for Errors and click OK;
Quickly select all formula errors

4) Now that only the cells containing error formulas are selected, simple press the Delete key on your keyboard.
Find and clear formula errors


What if you want to clear formulas you've filled down that don't result in errors?

Answer: You filter a column in your table for blanks and then clear the formulas from the visible cells.

Here's how...

1) Click any cell in your table and, from the Data menu, select Filter. Filter arrows should now be on the top of each column;

2) Click one of the filter arrows in a column that contains blank cells and choose Blanks. You should now see only the formulas that you filled down and you want to delete;

3) Highlight the cells containing the formulas you want to delete, hold down the ALT key and press the semicolon (;) key to select only the 'visible cells';

4) Press Delete to clear the formulas;

5) Click the Filter command on the Data tab to turn off the AutoFilter dropdown arrows.
8/4/14

Using Named Formulas Across Workbooks

Excel allows you to define names that refer to specific cells or ranges of cells in a workbook. In the same manner (using the Define Name tool on the Formulas tab of the ribbon) you can assign a formula to a name, and then use that name in place of the formula throughout the workbook.

A named formula is part of a collection in workbook object. This is why it can be used across different sheets in the same workbook and (in most cases) acts like it is part of the same "sheet" for many functions and routines.

To use a name in another workbook, your workbook must have a link to that name in the other workbook. There are a couple of ways to do this. The first is to link to the named formula with a formula like this:

='C:\Folder\Path\Filename.xls'!NamedFormula  

This can be copied in multiple cells. The other way is to create a name in the workbook (it can be the same or different than the name in the other workbook). Just display the New Name dialog box (click the Define Name tool on the Formulas tab of the ribbon) and use the following in the Refers To field:

='C:\Folder\Path\Filename.xls'!NamedFormula  

And now the workbook has a name and it refers to the named formula in the other workbook.

Both techniques create a "link" to the original workbook. There is one problem with either of these methods, however. Many simple formulas (the "direct links," like named ranges) will work even if the original file is closed. The more complicated formulas (which act like "indirect links," formulas with offset or other functions) will give a #REF! error if the original workbook is closed. In this latter case, the references will work only if both workbooks are open.

5/26/14

Simple Tricks For Hiding A Cell's Contents

Sometimes you may need to include one or more values in your worksheet but you do not want them to be displayed or printed. Some people resort to storing those values in some far corner of the worksheet, in a hidden row or column, or even storing them on another worksheet.

Here are a couple of tricks that you may want to try instead.

One option is to simply change to font color for those cells to match the background color (usually white), thereby making the cell contents invisible.

The second is to simply hide the values by applying a special custom number formatting code to the cells.

1) Select the cells you wish to hide;
2) Press Ctrl+1 to open the Format Cells dialog box;
3) Select the Number tab, and from Category, select Custom;
4) In the Type box, enter three semicolons (e.g. ;;;)

This number formatting code tells Excel that if the cell contents are either a positive number, a negative number, a zero value, or text, display nothing.
Hide cells from printing in Excel 2007


Note, however, that when a cell is selected, its contents are visible in the Formula bar.

To hide a cells contents in the Formula Bar...

First, change the cell's Hidden property...

1) Select the cells you want to hide from the Formula Bar;

2) On the Home tab click Format in the Cells group;

3) Click the Protection tab and place a checkmark in the Hidden property;

Next, protect the worksheet...

On the Review tab, click Protect Sheet in the Changes group, (Password optional) and click OK.
5/13/14

How To Use A Shape For A Macro Button

Are you still using Form Control buttons for launching you're macros?
How to use any shape or object in Microsoft Excel as a macro button
If you'd like spice things up a little and use something more visually appealing than a plain gray box with black letters, consider using an AutoShape...which gives you WAY more options for cool formatting.

You won't be able to replace the ActiveX version of the Command Button using this method, but for your basic 'click and run' macro button, this is a pretty cool option.

Here's how to replace that boring gray command button with a really nice-looking 3D 'Rounded Rectangle' AutoShape.

1)From the Insert tab click Shapes;

2) Pick a shape (I usually use Rounded Rectangle) and drag to draw the button on your worksheet;
Create a 3D Rounded Rectangle for you macro button
3) To assign a macro to the button, right click on the shape and select Assign Macro...;
Assign AutoShape as a button to run your macro
4) Select the workbook containing the macro in the 'Macros in' dropdown and select the macro name from the list and click OK;
Assign AutoShape as a button to run your macro
6) To make the button look 'way cooler', right click on it and you will see a new Drawing Tools tab appear on the right of the Ribbon tabs. Click on the Drawing Tools tab;
Make you AutoShapes look awesome with options from the Drawing tools tab


7) In the Shape Styles group you will see 3 sample styles. Click the dropdown on the right for More styles. You will get a palette of 42 shape styles to choose from;
Make you AutoShapes look awesome with more styles

8) Choose one of the styles on the bottom row. Now you've got a cool-looking macro button;
Make you AutoShapes look 3D in Microsoft Excel
9) With the button selected, type the text you want to display on your macro button;
How to add text to your AutoShapes in Microsoft Excel

10) You can adjust the alignment of the text on the button by using the Alignment commands (vertical and horizontal) on the Ribbon
How to align text in your AutoShapes in Microsoft Excel
11) To change the formatting of the button (e.g. background color, font, etc...) right click on the button and choose from the options that pop up.
How to change formatting of a Microsoft Excel AutoShape

12) Now, which one looks better?
How to change formatting of a Microsoft Excel AutoShape


13) If you decide you'd like a different shape after you have your button created, right-click the button to select it, click on the Drawing Tools tab, click Edit Shape from the Insert Shapes group, select Change Shape... and choose a different shape.
How to change an AutoShape's shape

This may seem like a lot of steps but, once you get the hang of it, it will take only a few seconds each time.

Go ahead and give it a try!

Adding a formatted AutoShape like this to your worksheet is not limited to macro buttons. You can create a button just like this to use as a hyperlink or simply to have a cool-looking button on your worksheet that does nothing.

4/29/14

Sort Your Data Sideways

Excel's default setting for sorting is to sort rows. However, sometimes you may want to arrange your columns in alphabetical or numerical order instead. Here's how sort your columns...

1) Select the range of data you want to sort (or select just a single cell in the table);

2) On the Data tab, click the Sort command in the Sort & Filter group;

3) In the Sort dialog, click the Options... button;

4) Select the Left to Right option and click OK;

5) In the 'Sort by' dropdown, select the row number containing the values you want to base your sort on. Note that you don't always have to sort your data based on the 'top' row of data. Sometimes you may need to sort your columns based on the values of a row in the middle of your list;

6) Choose a sort order and click OK.
Sort your columns in Excel 2007


It is always a good practice to save your work before you sort. You can Undo after the sort but if you mess things up, you can really cause yourself a lot of grief. Sometimes, making a backup copy of your workbook is a good idea too because it may be days later before you realize you messed up.

Another important thing to remember about sorting is, if you have a range of cells selected, Excel will sort only the data in those selected cells. You can allow Excel to select the sort range by selecting only one cell anywhere in that range. Excel will expand the sort range to include all cells in the 'current region' (bounded by blank columns and rows or the edges of the worksheet). When you click the Sort command, verify that the selected sort region is correct, if it isn't, manually select the sort range yourself.

4/15/14

Calculating Averages by Date

Suppose that you have a huge worksheet that contains all the rainfall readings for a given locale for the past hundred years or so. In cells A2:A37987 you have the dates, 1 January 1903 through 31 December 2006. In cells B2:B37987 you have the measurements for each date. Further, some of the measurements can be zero (if there is no rainfall for the day) or blank (if no reading was taken that particular day). With all this information, you want to calculate the average historic rainfall for any given day of the year.

One solution involves the use of array formulas, as detailed here:

  1. Select all the cells in column A that contain dates, and assign this range the name Dates.
  2. Select all the cells in column B that contain rainfall data, and assign this range the name Rainfall.
  3. In column D, starting in cell D2, place all the days of the year. You should end up with D2 through D366 filled with dates.
  4. In cell E2, enter the following array formula (terminate the formula by pressing Shift+Ctrl+Enter). The result of the formula is the sum of all the cells in the Rainfall range, for the date specified in cell D2.
  5. =SUM((MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2))*Rainfall)  
  6. In cell F2, enter the following array formula (terminate the formula by pressing Shift+Ctrl+Enter). The result of the formula is the number of cells in the Rainfall range, for the date in cell D2, that have a value in them.
  7. =SUM((MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2))*(Rainfall<>""))  
  8. In cell G2, enter the following regular formula. This is your average for the date in cell D2.
  9. =IF(F2<>0,E2/F2,"")  
  10. Select the range E2:G2 and copy down for all the dates shown in column D.

This approach works, but it takes quite a while to calculate. This is because you effectively entered 730 array formulas, each checking over 37,000 cells. This is a lot of work, and consequently it may appear like your machine has "hung" after you complete step 7. It has not hung; it will just take it a while to complete the calculations.

To decrease the number of calculations that must be performed, you can use a variation on the above steps. Follow steps 1 through 3, as noted, and then place the following array formula into cell E2:

=AVERAGE(IF(ISNUMBER(Dates)*ISNUMBER(Rainfall)*  (MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))  

You can then copy the formula down for all the dates shown in column D. The result of this formula is the actual average rainfall, the same as had been shown in column G in the previous approach.

This formula works because of the way that Boolean arithmetic works in Excel. The ISNUMBER function returns either True or False, and the comparisons (MONTH and DAY) return either True or False. These results are all multiplied against each other, resulting in True only if all the individual tests are True. Only if they are all True will the average of the Rainfall for that particular date be calculated.

You can reduce the calculation overhead even further by simply getting rid of all the table that calculates the averages for every day of the year. With your dates and rainfall in columns A and B, follow these steps:

  1. Select all the cells in column A that contain dates and assign this range the name Dates.
  2. Select all the cells in column B that contain rainfall data and assign this range the name Rainfall.
  3. In cell D2, place the date for which you want to check the average rainfall. (The year isn't important; only the month and day are used in the calculation.)
  4. Enter the following formula into cell E2:
=AVERAGE(IF(ISNUMBER(Dates)*ISNUMBER(Rainfall)*  (MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))  

That's it. Now, you can change the date in cell D2 as desired, and cell E2 will always indicate the average rainfall for that date. The formula in cell E2 is the same as the formula used in the last approach; the difference is that you aren't calculating it for all the days in a year, and thus the calculation is done much quicker.

Another approach involves the use of Excel's filtering capabilities. Before you can use them properly, however, you must create a column that shows only the month and day for each date in your data. Use this formula in cell C2:

=MONTH(A2) & "-" & DAY(A2)  

Now, turn on AutoFiltering (display the Data tab on the ribbon | Sort & Filter group | Filter) and use the drop-down list at the top of the new column to select the date for which you want an average. You then use the following formula, placed in any cell desired, to show the average rainfall for the selected date:

=SUBTOTAL(1,B:B)
2/11/14

Show Pivot Table Subtotals Below Your Data

Don't you hate it when every time you create a Pivot Table the subtotals are displayed above the data. I know I do.

When we add subtotals to our worksheets we typically put subtotals and totals below the data that make them up. So, why do Pivot Tales put subtotals on top? Beats me!
Excel 2013, 2010, 2007, 2003: Show Pivot Table subtotals below your data


If you want to show the subtotals below your data you can.

1) Click in the Pivot Table to bring up the 'Pivot Table Tools' tab on the Ribbon;

2) Below the Pivot Table Tools tab click the Design tab;

3) On the left end of the ribbon, click the Subtotals option in the Layout group;

4) Click 'Show all Subtotals at Bottom of Group'.
12/16/13

Stop 'Enable Macros' Message After Macros Have Been Deleted


Sometimes we 'inherit' old workbooks in which the original owner used macros that no longer work or are no longer useful. Maybe you have even deleted the macros but are still being prompted to 'Enable/Disable macros' each time that workbook is opened.

Disable Enable Macros prompt


The reason for the prompt is that, although the macros have been deleted, the modules in which the macros were stored have not. Each time you open a workbook, Excel checks to see if it contains modules. If it does, even if they are empty, Excel assumes the workbook contains macros and, therefore, prompts you to Enable / Disable them.

To prevent the Enable / Disable Macros prompt in workbooks that have no macros, you will have to delete the modules from the workbook. Here's how...

I highly recommend that you BACKUP YOUR WORKBOOK before attempting this.

Remove modules to stop Enable Macros prompt

1) Press ALT+F11 to open the Visual Basic Editor (VBE);

2) On the left you should see the Project Explorer pane, with a tree structure similar to Windows Explorer, containing your open workbooks (shown as VBEProjects). If you don't see the Project Explorer, press CTRL+R;

3) If the Modules folder for your workbook hasn't been expanded, click the + to expand it;

4) Right-click each module object in the Modules folder and selectRemove Module. You will be asked if you want to Export the Module. Select No;

5) Close the VBE window and return to Excel.

6) Save and Close the workbook.

When you re-open the workbook, the ‘Enable macro’ message should not appear. 

12/9/13

Displaying a Hidden First Column

Excel makes it easy to hide and unhide columns. What isn't so easy is displaying a hidden column if that column is the left-most column in the worksheet. For instance, if you hide column A, Excel will dutifully follow out your instructions. If you later want to unhide column A, the solution isn't so obvious.

To unhide the left-most columns of a worksheet when they are hidden, follow these steps:

  1. Press F5. Excel displays the Go To dialog box. (See Figure 1.)
  2. excel

    Figure 1. The Go To dialog box.

  3. In the Reference field at the bottom of the dialog box, enter A1.
  4. Click on OK. Cell A1 is now selected, even though you cannot see it on the screen.
  5. Make sure the Home tab of the ribbon is displayed.
  6. Click the Format tool in the Cells group, then choose Hide & Unhide, then Unhide Columns.

Another way to display the first column is to click on the header for column B, and then drag the mouse to the left. If you release the mouse button when the pointer is over the gray block that marks the intersection of the row and column headers (the blank gray block just above the row headers), then column B and everything to its left, including the hidden column A, are selected. You can then display the Home tab of the ribbon and click Format | Hide & Unhide | Unhide Columns.

A third method is even niftier, provided you have a good eye and a steady mouse pointer. If you move your mouse pointer into the column header area, and then slowly move it to the left, you notice that it turns into a double-headed arrow with a blank spot in the middle as you position the pointer over the small area immediately to the left of the column B header. This double-headed arrow is a bit difficult to describe; it looks most closely like the double-headed arrow that appears when you position the pointer over the dividing line between column headers. It is different, however, because instead of a black line dividing the double arrows, there are two black lines with a gap between them.

When your mouse pointer changes to this special double-headed arrow, all you have to do is right-click and choose Unhide. Your previously missing column A magically reappears.

11/19/13

Supress #DIV/0! Errors

Supress division by zero errors (#DIV/0!)One question I'm asked quite often is how to get rid of the #DIV/0! errors.

It's common to get #DIV/0! errors in worksheets that contain formulas with division operations. This error results from the divisor part of a division formula referring to a cell that is blank or has a zero value.

There is no perfect solution for this problem. Most times you'll want to know if a formula is trying to divide a number by zero, but there may be times where you want to suppress this error for reporting purposes.

You can modify your formula to display either a blank or a zero whenever the formula results in an error. Use one of these formulas:
=IF(D3=0,"",C3/D3) or =IF(D3=0,0,C3/D3)

Disable Enable Macros prompt

Keep in mind that, technically, a 'blank' or 'zero' answer is incorrect. You cannot divide a number by zero. Use this with caution.
11/12/13

A Neat 'Change Formulas to Values' Trick

When you're new to Excel you quickly find a need to convert a range of formulas to their fixed values. Maybe for a while the only way you knew was to type the value in each cell, overwriting the formulas. Then someone showed you a much easier way: Right-click, Copy, Right-click, Paste Special.., Values, OK. You thought, "Wow! That is awesome." That will save me a lot of time.

A new trick to convert formulas to values
Well, here's another neat trick even experienced Excel users may not have seen before.

1. Select the range you want to convert;

2. Move the cursor to the edge of the range;

3. Using your right mouse button, drag the range either to the left or right and then back to the original location and release the right mouse button;

4. You'll see a popup menu. Select Copy Here as Values Only and your formulas will be replaced with their fixed values.

If, rather than overwriting the original formulas, you want to copy the formula results to another range, right-drag the range to the new location and when you release the right mouse button, choose Copy Here as Values Only.

10/21/13

One of the form controls you can place in your worksheet is called alist box. This is a scrollable list of options from which the user can choose. You might use these in a worksheet if you have a group of options from which you want the user to choose. For instance, let's assume you have a worksheet that provides a price quote and the price is contingent on which color of widget the user wants. Having the users type the name of a color is open to error. For instance, they might enter the name of a color you don't carry, misspell the color name, or refer to a color as "gray" when your terminology is "slate." Rather than have them type a color in, you can present the various colors in a list from which they can choose.

To place a list box control in your worksheet, display the Developer tab of the ribbon, click the Insert tool (in the Controls group), and then click the List Box tool in the Form Controls section. You then use the mouse to define the rectangle that will hold the list box and the scroll bar at the right side of the box.

To use a list box effectively, you have to link it to two separate areas on your worksheet. The first is called an input list, and represents the options in the list. The second is the cell link, which contains the currently selected option. To set these areas, follow these steps:

  1. Choose the list box control (the one you placed on your worksheet) by clicking on it once.
  2. Click on the Properties tool in the Controls group on the Developer tab of the ribbon. Excel displays the Format Control dialog box.
  3. Make sure the Control tab is selected.
  4. In the Input Range field, supply the name or address of the cell range that contains the options to be included in the list box.
  5. In the Cell Link field, specify the cell that will contain the value indicating which option is selected.
  6. In the Selection Type portion of the dialog box you can specify how many options in the list can be selected at the same time. When you use list boxes on a regular Excel worksheet, it is important to leave this option set to Single. The other options only have meaning if you are creating a custom dialog box.
  7. Click on OK.

You should note that the Cell Link setting specifies a bi-directional relationship between the cell and the control. This means that a change in the selected option will change the value in the cell, but a change in the cell value will also change which option is selected in the list box. The value stored in the linked cell indicates the number of the option selected in the list. Thus, if you have a list of seven options and the second option in the list is selected, the value in the linked cell will be 2.

10/14/13

Sending Single Worksheets via E-mail

  1. Right-click the tab for the worksheet you want to e-mail.
  2. From the resulting Context menu, choose Move or Copy. Excel displays the Move or Copy dialog box. (See Figure 1.)
  3. excel

    Figure 1. The Move or Copy dialog box.

  4. Using the To Book drop-down list, choose New Book.
  5. Make sure the Create a Copy check box is selected.
  6. Click OK.

At this point, you should see a new workbook with a single worksheet in it—a copy of the worksheet you want to send. E-mail this workbook, and you've accomplished what you wanted to do. Once it is e-mailed, you can delete the workbook, as your worksheet is still in the original workbook, as well.

9/20/13

Date Functions

If you are in charge of tracking employees in your department or company, you might want to know if you can use Excel to calculate the months of tenure for those employees, given the date at which the employee started. This can be done very easily.

For the sake of this example, let's assume that column C contains the starting date for a list of employees. You could use the following formula in column D to determine each employee's tenure:

=DATEDIF(C3,NOW(),"M")  

The DATEDIF function calculates the difference between a starting date and an ending date. The "M" used in the formula indicates that you want the result in completed months.

To calculate the average tenure for your series of employees, simply include the following formula at the bottom of column C:

=AVERAGE(C3:C174)  

Of course, you should replace the range in the function (C3:C174) with the actual range of employee tenures as determined by the DATEDIF formula.

9/6/13

Adding a Drop Shadow to a Text Box

Text boxes are a graphic element you can use in your workbooks to hold information that is ancillary to the main data in the workbook. For instance, you might use a text box to create a sidebar or other text object. To enhance your layout design using text boxes, you can add a drop shadow so the text box appears to float about the actual printed page.

To add a drop shadow to a text box, follow these steps:

  1. Select the text box you want to format. Small selection handles should appear around the perimeter of the text box.
  2. Select the Format tab on the ribbon. (This tab is available only when you select the text box in step 1.)
  3. Click Shape Effects in the Shape Styles group. Excel displays a list of effects you can apply to the text box.
  4. Highlight the Shadow option and you will see a palette of available shadows.
  5. Click on the shadow desired.
9/2/13

You already know how to merge cells within a table. (If you don't know, a quick search at the WordTips site will reveal the information you need.) Once cells have been merged, you can later split them apart using many of the same methods you used to merge them in the first place. Here's an easy way to do the splitting:

  1. Right-click the previously merged cell.
  2. Choose Split Cells from the resulting Context menu. Word displays the Split Cells dialog box. (See Figure 1.)
  3. ecel

    Figure 1. The Split Cells dialog box.

  4. Using the controls in the dialog box, specify the number of columns and rows into which the merged cells should be split.
  5. Click OK.

After you split the cells, the cell widths may be off a bit from the other cells in the table, and you may need to readjust them.

You can also split previously merged cells by using the tools on the ribbon in this manner:

  1. Display the Design tab of the ribbon. (This tab is only visible if the insertion point is somewhere within a table.)
  2. Click the Draw Table tool in the Draw Borders group. This is the one that looks like a pencil. The mouse cursor now looks like a pencil.
  3. Use the mouse cursor to draw cell lines in your table. Simply click and drag to draw each new cell line. When you release the mouse button, the cells appear as you have drawn them.
  4. When you are finished drawing, click on the Draw Table tool again or press the Esc key. This turns off the drawing mode.
8/19/13

Smoothing Out Data Series

When you create line charts in Excel, the lines drawn between data points tend to be very straight. (This makes sense; the lines are meant to connect the points.) You can give your graphs a more professional look by simply smoothing out the curves Excel uses at each data point. Follow these steps:

  1. In your chart, right-click on the data series that you want to smooth. Excel displays a Context menu.
  2. Choose Format Data Series from the Context menu. Excel displays the Format Data Series dialog box.
  3. Click Line Style at the left side of the dialog box. (See Figure 1.)
  4. Figure 1. The Line Style options of the Format Data Series dialog box.

  5. Select the Smoothed Line check box.
  6. Click on OK.
8/2/13

Display Dashes Instead Of Zeros

Sometimes you may want your report to display dashes instead of zeros, especially spreadsheets that contain a lot of zeros.

Display zeros as dashes in Excel 2007
You can accomplish this with a simple change to your current number format.

1) Select the cells where you want to apply the format;

2) In Excel 2007, on the Home tab in the Number group, click the dropdown and select More Number Formats. In Excel 2000-2003, from the Format menu select Cells (or press CTRL+1). This will display the Format Cells dialog;

3) On the Number tab select Custom in the Category list. If all of the cells you have selected have the same number format, that format will be displayed in the Type field (e.g. #,##0;-#,##0)

Excel number formats can contain up to 4 sections of code separated by semicolons. The first section applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text. So to display dashes instead of zeros, we have to edit the third section (zeros).

By default, most number formats consist of only two parts: a format code for positive numbers and a format code for negative numbers. Usually, there is no code defined for zeros, so the positive number format is applied for zero value cells.

4) To specify a format for zeros other than the default format, we must add (or change) the third section of the number format code. If there is no format for zeros we need to add a semi-colon for the third section and then type a dash. This tells Excel to display a dash in any cell with a value of zero.
Display zeros as dashes in Excel 2007

Optionally, you can type one or more blank spaces following the dash to force Excel to offset the dash from the right edge of the cell by the width of the space(s);
7/26/13

Printing Cell Comments In Excel


By default, when you print your worksheet, cell comments do not print. That is a good thing because most of the time we use cell comments it is usually just information for the user of the worksheet.

However, there may be times when you want to print comments, especially if they are an important part of the information in the worksheet.

Now, the option to print cell comments is pretty obscure in Excel, so you can be forgiven if you haven't found it. It can be found on the Page Setup dialog...but hold on...it's not as simple as that. Whether it is by design or a bug in Excel, the way you access the Page Setup dialog will determine if the print comments feature is available.

Here's how to print comments...

1. Note that comments must be visible to be printed. To make all comments visible, on the Review tab click the Show All Comments command in the Comments group. If you want to choose individual comments to make visible, you will need to right-click on each cell and select Show/Hide Comment;
Show or hide all comments in a worksheet
2) From the Page Layout tab click the 'dialog launcher' (the small arrow in bottom right corner of Page Setup group) to display the Page Setup Dialog;
Show or hide all comments in a worksheet
3) Click the Sheet tab;
Print comments option


4) On the right side of the Print section of the dialog, you will see a Comments option. Note that if you navigated to the Page Setup dialog via the Print Preview window, the Print Comments option will be grayed out. Is this by design or an Excel bug? If it's a bug, it's been there for 10 years (since Excel 2000).

Assuming you have followed my instructions in Step 2 and the option is not grayed out, you now have a choice of printing the comments as they are displayed on the sheet or you can have them printed after the sheet.

5) Before you Print, click thePrint Preview button on the Sheet tab of the Page Setup dialog to check that everything will print properly.
Print comments on or after worksheet

Print Only the Cell Comments

If you want to print only the comments from your worksheet, choose the 'At end of sheet' option (as described above) and, in Print Preview, note the sheet number(s) that the comments will print on. Now, when you click print, change the starting page of the Print Range to that page number.
Print only cell comments

7/19/13

Sequentially Inputting Information

You may need to enter a series of numbers in the first five columns of a particular row, or you may need to enter information just in a range of ten cells in a particular column.

To sequentially enter information in a range of cells, you should first select the cells. Notice that Excel leaves the top-left cell in the range as the input cell (it is white and outlined). The rest of the cells in the range are shaded, to show that they are selected.

Now all you need to do is start entering numbers. When you do, the value you enter is entered into the input cell. When you press Enterat the end of the value, Excel saves the value and moves the input cell to the next cell in the selected range. Excel will move the input cell either left to right, top to bottom or top to bottom, left to right.

7-5-13

Inserting Tomorrow's Date

You may want to insert tomorrow's date into a cell in your worksheet. This is easily done by simply creating a formula that includes the function for today's date, as in the following:

=TODAY() + 1  

This formula first determines today's date, and then adds 1 to it, resulting in tomorrow's date. You can then use Excel's formatting features to display the resulting date in any manner desired.

Besides displaying tomorrow's date, you can modify the formula to display a date at any time in the future. Simply change the formula so the desired number of days are being added to today's date. For instance, if you wanted to determine a date two weeks in the future, you could use the following:

=TODAY() + 14
5/31/13

Drop-Down List Font Sizes

Unfortunately, there is no way to change the size of the font. The font size used for some Excel controls is modified whenever you change the display properties in Windows, but none of those affect the drop-down font, either.

Because the font size increases in proportion to the zoom factor used for a worksheet, there is one potential workaround. You could increase the zoom factor a significant amount, say to 100% or even 125%. Then, select the data in the worksheet and decrease the font size and adjust column widths so you can see as much of the worksheet as you could at the 60% zoom factor. The change in worksheet font size should not affect the drop-down font size. That should still be tied to the zoom factor, and now be large enough to easily read.

5/17/13

Creating a CSV File

CSV is one of those over-abundant computer acronyms. It means "comma-separated values." It refers to a type of file that is often used for transferring simple data from one program to another. In the file, each value is separated by a comma. The importing program knows that when it sees a comma, it can toss it out but it needs to get ready to accept a new value.

If you have information in Excel that you want to get into a different data-oriented program, chances are good that the other program will accept CSV files. In order to save your workbook data in CSV format, follow these steps:

  1. Display the Save As dialog box. (In Excel 2007 click the Office button and then click Save As. In Excel 2010 display the File tab of the ribbon and then click Save As.)
  2. In the Save As Type drop-down list at the bottom of the dialog box, choose the CSV (Comma delimited) option.
  3. Use the other controls in the dialog box to specify a file name and location.
  4. Click on Save.
  5. Excel may display a dialog box telling you that not all Excel features can be maintained in the format you have chosen. Click on Yes to continue saving the information in CSV format.

At this point your worksheet is saved in CSV format. I generally find it a good idea to close the workbook, without saving. At this point you will have your original Excel workbook (in Excel format) and the CSV file, which contains the information you wanted in CSV format.

5/10/13

Create Custom Default Worksheet And Workbook Templates

Are you constantly having to change the settings for your worksheets, such as margins, font size, page headers, etc... every time you create a new workbook or insert an new worksheet? Most Excel users assume that's just the way it is. Well, I'm here to tell you those days are over.

I'm going to show you how to create a template containing your own personalized settings that will be applied to all new workbooks and worksheets.

When you open a new workbook, Excel uses a workbook template with default settings such as three worksheets, column widths of 8.43, no headers and footers, default print margins, etc... When you insert a sheet into a workbook, Excel uses a worksheet template with similar default settings.

Here's how to create your own personalized templates so that every new workbook and worksheet will already have the settings you want.

1) Open a blank workbook;

3) Delete all sheet tabs except for one;

4) If you want to change the default formatting of your worksheet cells, such as the font face, font size, number formats, etc..., on theHome tab, click the Cell Styles command in the Styles group. Then right click the Normal style and choose Modify. Click the Format button and make any changes you like to the default style and click OK;

5) Next, to select your preferred print settings, click the Page Layout tab. Make all the changes you want your default worksheets to have (i.e. margins, headers/footers, etc...);


Personalizing Your Default Worksheets?
·  Want a Custom Header or Footer to appear on every new worksheet? On the Page Layout tab, click the Dialog Launcher (small arrow on bottom right corner of 'Page Setup' group) and click the Header/Footer tab.
·  Want all new sheets to fit your data one page wide? From the Page Layout tab, select 1 Page in the Width field of the 'Scale to Fit' group.
·  Don't like the current print margins? On the Page Layout tab, click the Margins command in the 'Page Setup' group, then click Custom Margins...
·  Would you prefer the default orientation of all new sheets to be Landscape? On the Page Layout tab, click the Orientation command in the Page Setup group.
·  Don't like gridlines displayed in your worksheets? Click the View tab and uncheck Gridlines in the Show group.



6) When you have selected all of the custom settings for your default worksheets, click cell A1 to make it the active cell for all new worksheets;

7) Next, you need to save this custom worksheet as a template in your XLSTART folder. Click the File tab and Save As. In the Save as Type dropdown on the bottom of the dialog box, select Excel Template (*.xltx) and browse to the XLSTART folder.


Locating Your XLSTART folder

In Windows 7 you'll find your XLSTART folder at...
C:\Users\username\AppData\Roaming\Microsoft\Excel\

In Windows XP you'll find it at...
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

Another option for quickly locating your XLSTART folder is to type or paste the following command into the Windows Explorer Address Bar or even into the 'File Name' field of Excel's Save As or Open dialog boxes and press Enter.
%APPDATA%\Microsoft\Excel\XLSTART

Easily locate your XLSTART directory / folder
Or try this...

1. Press ALT+F11 to open the Visual Basic Editor;
2. Press ALT+G to open the Immediate window;
3) Type ? application.StartupPath and press Enter.
Use the VBE Immediate window to locate the XLSTART folder in Excel 2007 and 2010



Change the suggested name to SHEET.XLTX. This will now be the default sheet template used when you Insert a worksheet into a workbook;

9) Now you need to save this same personalized worksheet as your default 'workbook' template by saving it with a different file name. To do this, simply repeat Step 7 but this time save the workbook with the filename BOOK.XLTX;

Personally, I like my default workbook to have only one sheet rather than the default three sheets Excel offers, as I find the extra two sheets redundant. If you choose to go with a one sheet default workbook you can quickly insert a new sheet by clicking the Insert Worksheet command (immediately to the right of the sheet tabs). However, in workbooks with a lot of sheet tabs, the Insert Worksheet command may not be visible, so I recommend adding the Insert Worksheet command to your Quick Access Toolbar (QAT). Alternatively you can use Shift+F11 to insert a new sheet.

Add the Insert Worksheet and New workbook commands to your QAT

1) Right-click the QAT and choose Customize Quick Access Toolbar;

2) In the Choose commands from dropdown, select All Commands;

3) Scroll down and select the Insert Worksheet command and click the Add>> button;

4) Scroll down again and select the New Workbook command and click Add>> button. Note that the name in the list for this command is New, not New Workbook.
Add the Insert Worksheet command on the QAT


Now, whenever you open a new workbook or insert a new worksheet it will always contain your preferred settings.

If you chose to have only one sheet in each new workbook, you can easily insert additional sheets whenever needed by clicking on the Insert Worksheet button.

Remember that if you ever get a new computer or work on multiple computers, you can copy these two files (SHEET.XLTX AND BOOK.XLTX) so you won't have to create them again.
53/13

Running Macros on Hidden Worksheets

Macros are often used to process information within a workbook. Your macro can access any cells in the workbook, unless the worksheet containing the cell is hidden. When you hide a worksheet, it is even hidden from normal macro operations.

The upshot of this is that if you want to run a macro and have it access information on a hidden worksheet, you must first "unhide" the worksheet. To do this, you use the following line of code in your macro:

Sheets("My Hidden Sheet").Visible = True

When this line is executed, then the worksheet named My Hidden Sheet will no longer be hidden. It is then easily accessible by regular macro commands. When you are later ready to hide the worksheet again (when you are done processing), use this line of code:

Sheets("My Hidden Sheet").Visible = False

Of course, unhiding and later hiding worksheets can cause a lot of flashing on the screen as Excel tries to update its screen display based on the commands executed in your macro. If you want to avoid this, then use the following line of code at the beginning of your macro:

Application.ScreenUpdating = False

With screen updating turned off in this way, nobody will ever know that you unhid a worksheet and later rehid it. Make sure that before ending the macro, however, you set the ScreenUpdating property back to True.

4/1/13

Locking Worksheet Names

If you are developing workbooks for others to use, you may want your worksheets to retain whatever names you give them. Excel normally allows users to change worksheet names, as desired. If you don't want them to change, the only way to prevent it is to lock the workbook. You can take these steps:

  1. Display the Review tab of the ribbon.
  2. Click the Protect Workbook tool in the Changes group. Excel displays the Protect Structure and Windows dialog box. (See Figure 1.)
  3. excel

    Figure 1. The Protect Structure and Windows dialog box.

  4. Make sure that the Structure check box is selected.
  5. Enter a password in the Password box.
  6. Click on OK. Excel displays the Confirm Password dialog box, prompting you to reenter the password.
  7. Reenter the password and click on OK.

The user can no longer make changes to the names of the worksheet tabs, nor to anything else that affects the structure of the workbook. (For instance, they cannot enter new worksheets or delete existing ones.)

3-19-13

Changing Axis Tick Marks

If you use an Excel chart type that uses axes, you may have noticed the presence of "tick marks" on one or all of the axes. Tick marks are used to indicate a major or minor demarcation along an axis. For instance, if you have an axis that ranges from 0 to 1000, there may be major tick marks at every 100 in the range, and minor tick marks at every 50.

Excel normally sets up the tick marks for you, but you can change the way they appear by following these steps:

  1. Right-click on the axis whose tick marks you want to change. Excel displays a Context menu for the axis.
  2. Choose Format Axis from the Context menu. (If there is no Format Axis choice, then you did not right-click on an axis in step 1.) Excel displays the Format Axis dialog box.
  3. Make sure the Axis Options tab is selected. (See Figure 1.)
  4. Excel

    Figure 1. The Axis Options tab of the Format Axis dialog box.

  5. To the right of Major Unit, click Fixed and specify a multiple at which you want the major tick marks to appear.
  6. To the right of Minor Unit, click Fixed and specify a multiple at which you want the minor tick marks to appear.
  7. Click on OK.
3/1/13

Auto Fill

The AutoFill feature of Excel is a great timesaver when you need to fill in a series of cells based on the content in other cells. It is also a feature that you can turn off, if you prefer. (For instance, the AutoFill handle visible at the bottom-right corner of a selection may be distracting to you, and you want to turn it off.)

To turn off AutoFill, follow these steps:

  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 click Options.)
  2. Click the Advanced option at the left of the dialog box. (See Figure 1.)
  3. Excel

    Figure 1. The Advanced options of the Excel Options dialog box.

  4. In the Editing Options area, clear the Enable Fill Handle and Cell Drag-and-Drop check box.
  5. Click on OK.

Besides turning off AutoFill, the above steps also turn off the ability to drag the contents of a cell from one location to another.

3/1/13

Setting Stable Column Widths in a PivotTable

Stuart has been building PivotTables for a charity so that they can report on donation patterns over time. The problem he has is that he wants to permanently set the column widths in his PivotTable, but whenever the filter variables for the PivotTable are changed, the column widths reset themselves. Stuart wonders how he can permanently fix all column widths in the PivotTable so they don't change.

This is very easy to do in Excel. After your PivotTable is set up and formatted just the way you want, follow these steps:

  1. Select a cell in the PivotTable.
  2. Display the Options tab of the ribbon.
  3. Click the Options tool in the PivotTable group. Excel displays the PivotTable Options dialog box.
  4. Make sure the Layout & Format tab is displayed. (See Figure 1.)
  5. excel

    Figure 1. The Layout & Format tab of the PivotTable Options dialog box.

  6. Make sure the Autofit Column Widths on Update check box is cleared.
  7. Click OK.
2/1/13

Easy Value Hiding

Excel provides many different ways to hide the content of your workbooks. Perhaps one of the easiest (and most novel) ways is to use the custom formatting feature of the program. Follow these steps:

  1. Select the cells whose content you want to hide.
  2. Display the Home tab of the ribbon.
  3. Click the small icon at the bottom-right of the Number group. Excel displays the Format Cells dialog box.
  4. Make sure the Number tab is displayed. (See Figure 1.)
  5. excel

    Figure 1. The Number tab of the Format Cells dialog box.

  6. In the list of format categories, select Custom.
  7. In the Type box, enter three semicolons (;;;).
  8. Click on OK.

Now the information in the cell is not visible, nor will it print. You can, however, see the information in the Formula Bar, and it can be overwritten if you enter anything else in the cell.

 

Generating a Web Page

Creating a new Web page from an existing workbook is very easy in Excel. All you need to do is load and display the information you want used as your source material and then click F12 to display the Save As dialog box. Make sure you then change the Save As Type drop-down in the Save As dialog box to Web Page. (See Figure 1.)

Excel

Figure 1. The Save As dialog box.

If you have used Excel for a while, you probably noticed right away that there are some differences between this incarnation of the Save As dialog box and the normal one used for saving information to a new workbook file. In this case, Excel includes additional controls that allow you to specify how it should create your Web page.

Using the controls in the Save As dialog box to indicate a file name and directory should be pretty straight-forward. You need to indicate in the middle of the dialog box what you want saved in your Web page, however. You can specify to save either the entire workbook or you can save the worksheets you selected before displaying the Save As dialog box.

In addition, if you choose to save just a worksheet, you can specify that the output be made interactive. This means that others who access the Web page can utilize the information in much the same way as they could with a native Excel file, provided they have a compatible version of Excel on their system.

Notice, as well, that you can specify a page title for your Web page. The page title is displayed by a browser at the top of the page, in the title bar. To change the page title, click your mouse on the Change Title button.

When you are ready to save your Web page, simply click on the Save button. Excel generates the HTML output file according to your specifications. You can then place the generated Web page file on a Web server for all the world to see.

People viewing your page don't need to have Excel; they just need to have a standard Web browser. Exactly how your information is displayed may differ from one browser to another. Standards within the Web leave it up to browser to determine how characteristics are displayed. Thus, one browser may display columns or fonts or bold text a bit differently than is done in a different browser. It is impossible for Excel to anticipate and overcome these differences.

11/9/12

Opening Multiple Excel Workbooks at Once

Excel provides a complete file management system. One of the features of this system is that you can quickly load multiple workbooks in one easy step. To do this, follow these steps:

  1. Display the Open dialog box. (In Excel 2007, click the Office button and then click Open. In older versions of Excel click on Open or choose Open from the File menu.)
  2. Instead of double-clicking on a file name, start to create a "selection set." If you are selecting sequential files, click on the first file in the sequence, then hold the Shift key as you click on the last. You can add additional files to those selected by holding down the Ctrl key while clicking with the mouse.
  3. To remove files from the selection set, hold down the Ctrl key while clicking on a file name you previously added to the set.
  4. When you are satisfied with the list, click on Open.
10/26/12

Excel - Protecting Worksheets

Sometimes you may have a need to prevent changes to information in a particular worksheet in your workbook. For instance, you may want to send a worksheet to others for their review, but you don't want them changing the information or formulas in the worksheet.

Excel makes it very easy to protect one or more worksheets in any of your workbooks. You can do this by first specifying which cells in the worksheet should be protected, and then locking the worksheet.

Protection of your formulas or data is done on a cell-by-cell basis. This may seem odd, but it can be very handy. What it means is that you can protect all the cells in a worksheet, but leave the user input cells unprotected. When a user loads the worksheet, they can make changes to the user input values, see the calculations based on them, and get the information they need. The benefit to you is that you don't need to worry about users deleting information or changing formulas by mistake.

By default, cells are locked, pending your application of protection to the entire worksheet. What this means is that even though cells are locked, users can still change the cells unless you protect the worksheet, as described shortly.

If you want to change the condition of a particular cell so that it is not locked, you do so in this manner:

  1. Select the cell (or cells) you want to change.
  2. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is selected. (See Figure 1.)
  4. worksheets

    Figure 1. The Protection Tab of the Format Cells dialog box.

  5. Set the Locked check box to reflect how you want the selected cells treated when you protect the worksheet.
  6. Set the Hidden check box to indicate whether the formula in a cell should be hidden from view when the worksheet is protected.
  7. Click on OK.

Once you have specified how you want the individual cells treated, you still need to protect the worksheet as a whole by locking it. This step can be viewed as a way to "turn the key" in the locks you put in place for each cell.

You do this by selecting the Protection option from the Tools menu. This displays a submenu from which you should select the Protect Sheet option. The result is the Protect Sheet dialog box. At the top of the dialog box you specify what should be protected. You have three choices:

  • Contents. Enforces any protection you applied to individual cells, as described earlier.
  • Objects. Ensures the user cannot change placement or attributes of any graphics or other objects used in your worksheet.
  • Scenarios.Protects any scenario definitions you have created, as discussed in previous issues ofExcelTips.

At the bottom of the dialog box you can specify a password to be used for unlocking the worksheet. For maximum protection, you should use a password that is easy for you to remember, but would be difficult for others.

When you click your mouse on the OK button, what happens next depends on whether you specified a password. If you did not, the worksheet is immediately protected. If you did use a password you will see a dialog box asking you to again enter the password. This is a double-check to make sure you did not mistype the password the first time. When you click your mouse on OK in this dialog box, the worksheet is finally protected.

10/12/12

Automatically Updating Charts for Additional Data

Excel shines at turning your data into charts—graphical representations of your data. You can easily create a chart based on a range of data in a worksheet. Normally, if you add additional data to your range, you will need to once again create the chart or at best change the range of cells on which the chart is based.

If you get tired of modifying charts to refer to new data ranges, there are a couple of shortcuts you can try out. The first shortcut works fine if you simply need to "fine tune" the range used in a chart. (This approach only works if the chart is on object on the same worksheet that contains the data on which the chart is based.) Follow these steps:

  1. Enter any new data to the table on which the chart is based.
  2. Select all the cells that you just entered.
  3. Click anywhere on the selection border around the cells. (Don't click on the fill handle.)
  4. Drag the selection toward the chart and drop it on the chart.

That's it—Excel incorporates the new data right into the existing chart, slick as a whistle.

Another approach is to add new data to the range not at the end, but somewhere within the range. For instance, you may have some data that represents a time period, such as 11/1 through 11/13, and you create a chart based on those dates. If you add new data to the end of the range (after 11/13), then Excel doesn't know you want those items added to the chart.

Instead, insert some blank rows somewhere within the data range; it doesn't matter where, as long as the record for 11/13 is below the added rows. You can then add the new data in the new rows, and the chart is automatically updated to include the data.

One drawback to this approach, of course, is that the inserted data will be out of order when compared to the overall structure of the data table. It is interesting to note that for some types of data—such as those based on dates—Excel will automatically sort the data by date as it presents it in the chart, but not in the data table itself. You can always use Excel's sort feature to reorder the data in the table, all without affecting what is presented in the chart.

Still another approach is to create a "dynamic range." This approach works well if the data range you are charting is the only data on the worksheet. Follow these steps:

  1. Create your data table as you normally would. (To keep things simple, we'll assume that you are creating a two-column data table, in columns A and B, that you then want to chart.)
  2. Create your chart, as desired.
  3. With the worksheet visible, display either the Define Name or New Name dialog box (See Figure 1.) depending on your version of Excel. In Excel 2007 display the Formulas tab of the ribbon and, in the Defined Names group, click Define Name. In older versions of Excel click on Insert | Name | Define.
  4. img2

    Figure 1. The New Name dialog box.

  5. In the Names in Workbook or Name field (depending on your version of Excel) enter a name that will refer to the data in column A.
  6. In the Refers To field, enter the following formula. You should change the sheet name and beginning cell (in this case $A$2), as necessary. If you change the beginning cell, you should also change the final part of the formula (-1) to be one less than the row number of the beginning cell. Thus, if you change the beginning cell to $A$5, you should change the final part of the formula to -4.
  7.      =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)  
  8. Click OK (Excel 2007) or Add (older versions). Excel creates the name and associates it with the formula you entered. In versions of Excel prior to Excel 2007 the Define Name dialog box should still be visible, with the newly defined name listed.
  9. If you are using Excel 2007, again display the New Name dialog box by displaying the Formulas tab of the ribbon and clicking Define Name.
  10. In the Names in Workbook or Name field (depending on your version of Excel) enter a name that will refer to the data in column B.
  11. In the Refers To field, enter the following formula. (You should make the same type of changes to the sheet name, beginning cell, and final part of the formula as described in step 5.)
  12.      =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)  
  13. Click OK (Excel 2007) or Add (older versions). Excel creates the name.
  14. If you are using a version of Excel prior to Excel 2007, click OK to close the Define Name dialog box.
  15. Display the chart and select the data series. (There should only be one data series, since this is a simple two-column data table.) The formula bar will display a formula similar to the following:
  16.      =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$32, Sheet1!$B$2:$B$32,1)  
  17. Replace the ranges in the formula with the names you defined earlier in these steps. Assuming you defined the names Dates and Readings in steps 6 and 9, the formula should look like this:
     =SERIES(,Sheet1!Dates,Sheet1!Readings,1)  

Now the chart will update automatically regardless of where you add information in your data table. This works because the names you defined in steps 6 and 10 refer to a formula that calculates the extent of data in columns A and B of your worksheet.Excel shines at turning your data into charts—graphical representations of your data. You can easily create a chart based on a range of data in a worksheet. Normally, if you add additional data to your range, you will need to once again create the chart or at best change the range of cells on which the chart is based.

If you get tired of modifying charts to refer to new data ranges, there are a couple of shortcuts you can try out. The first shortcut works fine if you simply need to "fine tune" the range used in a chart. (This approach only works if the chart is on object on the same worksheet that contains the data on which the chart is based.) Follow these steps:

  1. Enter any new data to the table on which the chart is based.
  2. Select all the cells that you just entered.
  3. Click anywhere on the selection border around the cells. (Don't click on the fill handle.)
  4. Drag the selection toward the chart and drop it on the chart.

That's it—Excel incorporates the new data right into the existing chart, slick as a whistle.

Another approach is to add new data to the range not at the end, but somewhere within the range. For instance, you may have some data that represents a time period, such as 11/1 through 11/13, and you create a chart based on those dates. If you add new data to the end of the range (after 11/13), then Excel doesn't know you want those items added to the chart.

Instead, insert some blank rows somewhere within the data range; it doesn't matter where, as long as the record for 11/13 is below the added rows. You can then add the new data in the new rows, and the chart is automatically updated to include the data.

One drawback to this approach, of course, is that the inserted data will be out of order when compared to the overall structure of the data table. It is interesting to note that for some types of data—such as those based on dates—Excel will automatically sort the data by date as it presents it in the chart, but not in the data table itself. You can always use Excel's sort feature to reorder the data in the table, all without affecting what is presented in the chart.

Still another approach is to create a "dynamic range." This approach works well if the data range you are charting is the only data on the worksheet. Follow these steps:

  1. Create your data table as you normally would. (To keep things simple, we'll assume that you are creating a two-column data table, in columns A and B, that you then want to chart.)
  2. Create your chart, as desired.
  3. With the worksheet visible, display either the Define Name or New Name dialog box (See Figure 1.) depending on your version of Excel. In Excel 2007 display the Formulas tab of the ribbon and, in the Defined Names group, click Define Name. In older versions of Excel click on Insert | Name | Define.
  4. img1

    Figure 1. The New Name dialog box.

  5. In the Names in Workbook or Name field (depending on your version of Excel) enter a name that will refer to the data in column A.
  6. In the Refers To field, enter the following formula. You should change the sheet name and beginning cell (in this case $A$2), as necessary. If you change the beginning cell, you should also change the final part of the formula (-1) to be one less than the row number of the beginning cell. Thus, if you change the beginning cell to $A$5, you should change the final part of the formula to -4.
  7.      =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)  
  8. Click OK (Excel 2007) or Add (older versions). Excel creates the name and associates it with the formula you entered. In versions of Excel prior to Excel 2007 the Define Name dialog box should still be visible, with the newly defined name listed.
  9. If you are using Excel 2007, again display the New Name dialog box by displaying the Formulas tab of the ribbon and clicking Define Name.
  10. In the Names in Workbook or Name field (depending on your version of Excel) enter a name that will refer to the data in column B.
  11. In the Refers To field, enter the following formula. (You should make the same type of changes to the sheet name, beginning cell, and final part of the formula as described in step 5.)
  12.      =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)  
  13. Click OK (Excel 2007) or Add (older versions). Excel creates the name.
  14. If you are using a version of Excel prior to Excel 2007, click OK to close the Define Name dialog box.
  15. Display the chart and select the data series. (There should only be one data series, since this is a simple two-column data table.) The formula bar will display a formula similar to the following:
  16.      =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$32, Sheet1!$B$2:$B$32,1)  
  17. Replace the ranges in the formula with the names you defined earlier in these steps. Assuming you defined the names Dates and Readings in steps 6 and 9, the formula should look like this:
     =SERIES(,Sheet1!Dates,Sheet1!Readings,1)  

Now the chart will update automatically regardless of where you add information in your data table. This works because the names you defined in steps 6 and 10 refer to a formula that calculates the extent of data in columns A and B of your worksheet.

8/17/12

Jumping to a Specific Worksheet

Ifyou have a huge number of worksheets in a workbook, you may be looking for a way to jump to a specific sheet rather easily. There are a number of ways you can approach this task, and their applicability to your situation depends on how many worksheets you actually have in the workbook.

One option that works well if you have a limited number of worksheets (say, 30-40 sheets or less) is to right-click the sheet navigation buttons at the left of the sheet tabs. Doing so will pull up a list of worksheet names, and you can select which one you want to jump to. If there are more worksheets than can comfortably fit in the list, then one of the options is "More Sheets." Select that option, and you end up with a dialog box that lists all the worksheets and you can make your selection.

Another option that many people employ is to create a "table of contents" for your workbook. In the first worksheet, enter a bunch of hyperlinks that jump to the various worksheets in your workbook. That way you can display the TOC, click a link, and you are on your way.

If you know the name of the worksheet you want to jump to, you can also use the Go To capabilities of Excel. Follow these steps:

  1. Press F5. Excel displays the Go To dialog box.
  2. In the Reference box, enter Sheet83!A1. (Replace "Sheet83" with the name of the worksheet you want to jump to.)
  3. Click OK.
7/13/12

VLookup function

Suppose you had the following table:

a

Given a list of names in another part of the table (in this case, column H), you want to figure out what kind of animal it is:

d

Vlookup’s format looks like the following:

=vlookup(lookup value, table where values reside, column # where values are located, false)

Let’s look at each of these parts a bit closer.

The first thing that goes into the vlookup function is the thing you know (or are given) and that will be used to lookup other values. In this case, you have the names of the animals, so these are the things we know. In our example, they reside in column H, from cells H2 through H5. If we wanted to put the type of animal next to the name of the animal in column I (so I2 would correspond to the name of the animal in H2), we would insert the vlookup function there:

e

and put H2 as the first thing in our vlookup function:

f

Next, we need to know the location of the table where our values reside. These happen to be from cells A1 through B5 in this example, which we would highlight with our mouse to insert into the vlookup function. It’s very important that you include all the cells in the table.

Highlight the table with your mouse:

j

At the same time, the vlookup function automatically puts in the cells you’ve highlighted:

g

Next, we need the column number where the values are located. Always start with the first column (column A in this case) as #1 and count out to the right. In this example, the type of animal listed is in column 2, so that’s what we would need to insert in the vlookup function. Note that to use vlookup, your keys always have to be to the left of your values. (We’ll cover more of this in part II of the tutorial at a later date.)

h

Finally, the last attribute that vlookup takes is either “true” or “false”. I happen to always use “false”, and what this does is force vlookup to return the first exact value it finds. If that value isn’t found, then vlookup conks out and returns “#N/A”. Though we won’t use it in this example, if you select “true”, then rather than always looking for the exact value, vlookup will return the exact value if it exists, or the closest one to it that doesn’t exceed the key. (If you use “true”, you will need to sort your data in ascending order before using vlookup.)

i

Still with me? Again, this is what we would actually put in cells I2 if the names of the animals we have are located in cells H2 through H5:

=vlookup(H2, A1:B5, 2, false)

Once we close off the parenthesis and hit “Enter”, vlookup automatically calculates:

k

And so on. We would continue down each cell in column I that we needed. One thing to note is to make sure that the location of your keys and values is always selected correctly. Oftentimes, as you copy-and-paste formulas all around Excel, the location of the data will also move around relative to the cell. The easiest way to prevent this is to “lock” the range of the location; in this case, we would do so by using “$A$1:$B$5″ instead of “A1:B5″. This way, as we move down column I, say, to cell I2, A1:B5 doesn’t become A2:B6 but stays with the original range of data. This way, we can just copy what’s in cell I2 down the rest of the cells (from I3 through I5):

b

Finally, here’s our result, after making the “$” changes and copying and pasting the formula down the rest of the column:

c
3/30/12 Excel Shortcuts:

1. To format any selected object: ctrl + 1
2. To insert the current date: ctrl + ;
3. To insert the current time: ctrl + shift ;
4. To insert or edit a cell comment: shift + F2
5. To auto-sum selected cells: alt + =
6. To insert a new sheet: shift + F11
7. To go to the next worksheet: ctrl + pg down
8. To go to the previous worksheet: ctrl + pg up
9. To see formulas (and formatting): ctrl + ~ (repeat this command to return to original view)
1/27/12 Fill Blank Cells With The Data From Above

When you bring data into Excel from another program, often some of the cells are left blank, indicating a repeat of the value above. This may make the information easier to read but it also means that you cannot use many of Excel's best features, such as filtering, subtotaling and Pivot Tables, to manipulate the data.

If you have a small amount of data, you can simply fill down or Copy and Paste the values for each blank section. However, when you are dealing with hundreds or even thousands of records, this method would not be practical (unless you have nothing better to do and you really enjoy doing boring, monotonous work)

I'd like to show you a 'fairly simple' process to help you complete this same task in less than a minute, regardless of how many records you have.

For this example, I the data for which we want to fill the blank cells is in Column A...

1) Select all of the data, both blank and non-blank cells, in a single range (e.g. A2:A38);

2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog;

3) Select the Blanks option and click OK. Only the blank cells are now selected;

4) Type = and press the Up arrow key once;

5) Hold down the CTRL key and press Enter;. The puts a formula referencing one cell above in every cell that was previously blank;

6) Re-select the entire data range again (same as step 1);

7) Right-click, Copy;

8) Right-click, Paste Special...,Values, OK.
11/11/11

Ensure Values Entered In A Range Are Correct Length 

If you want to ensure that the correct number of digits/characters are entered for data such as part #s, serial #s, invoice #s, etc... you can do it using Data Validation.

1) Select the range of cells;

2) From the Data tab, click Data Validation in the Data Tools group and then Data Validation from the options. (Excel 2003 users, from the Data menu, select Validation);

3) Click the Settings tab and in the 'Allow' dropdown, select 'Text Length';

4) In the Data dropdown, select 'Equal to';

5) In the Length field enter 7 (the required length of the input data);

6) Optionally, you can customize the error message that appears when someone inputs an invalid value by clicking the Error Alert tab, entering a title for the error dialog and a message to the user.

7) Click OK.

It is important to note that, although Data Validation is very useful, there are some serious limitations which could possible cause problems.

·If you copy a cell that does not use Data Validation and paste it over a cell that does use Data Validation, the validations rules are overwritten and any value can be entered in the cell.
·Data Validation doesn't apply to the results of formulas.

·Unless the Stop option is selected in the Style dropdown on the Error Alert tab, any value can be entered.
8/19/11 Filtering
  1. In an open Excel file, select Data from the menu bar.
  2. Click on Filter
  3. Click a dropdown arrow in the column to be filtered.
  4. Put a checkmark by the data you would like to see.
  5. The list will change to display only the selected information.
  6. Turn off the filter by selecting the Filter again.
5/27/11

AutoSum

Everyone uses Excel's AutoSum button for quickly summing values. Did you know there is also a virtually unknown keyboard shortcut that performs the same function. This is great for times when you'd rather use your keyboard.
 
Simply press the Alt key plus the equal sign (Alt + =)

Once you get used to using this shortcut, you can avoid reaching for the mouse when you already have your hands on the keyboard.

4/25/11

How to Paste a Hyperlink


1) Open the workbook where you want to paste the hyperlink

2) Open the workbook that you want the hyperlink to jump to. This can be the same workbook or another workbook. This cannot be a new, unsaved workbook

3) Navigate to the cell you want the hyperlink to jump to

4) Copy that cell

5) Switch to the workbook where you want to paste the hyperlink

6) In Excel 2007-2010, on the Home tab, click the small arrow below the Paste button and click Paste as Hyperlink. In Excel 2000-2003, from the Edit menu select Paste As Hyperlink. Note that if the workbook you are copying from is a new unsaved workbook, the Paste as Hyperlink option will be greyed out;

The contents of the cell you are copying will determine what will be displayed in the hyperlinked cell.

If the copied cell contains text, that same text will be displayed in the cell containing the hyperlink.

However, if the copied cell contains a formula, a numeric value, or a blank cell, the full path (file name, worksheet name, cell reference) of the cell will be displayed in the cell containing the hyperlink. If the copied cell is from within the same workbook, only the sheet name and cell reference will be displayed.

Once you have pasted a hyperlink, you can change the text displayed in that cell without changing the hyperlink.

For example, you can copy a blank cell (A1) from another workbook and paste it as a hyperlink. The cell containing the hyperlink will now be showing the full path of the copied cell. You can then type some other text in that cell to replace the long displayed text. 

Now you can click on the hyperlink and instantly the source file will be opened and you will be taken to the linked cell.

4/1/11

Shortcuts keys

While in edit mode...

  • To jump to the beginning or end: Press Home or End;
  • To jump one word right or left: Press CTRL + left/right arrow keys;
  • To select text to copy: Drag with the mouse (or use the Shift + arrow keys).
  • To copy: Select text and press CTRL+C (or right-click, Copy);
  • To cut: Select text and press CTRL+V (or right-click and Paste);
  • To paste: Position insertion bar and press CTRL+V (or right-click, Paste);