Excel Video Tutorials

Quickly Minimize the Ribbon in Excel

2011-11-24T22:53:00.000-05:00

With a simple double-click, you can quickly minimize the Ribbon in Excel. Then, double-click to show the full Ribbon again. This tip works in both Excel 2007 and Excel 2010.

Minimizing the Ribbon is useful if you're working on a small monitor, or if you want to make more room for a large worksheet in Excel.

Create Excel Hyperlinks With Drag and Drop

2011-07-21T22:34:00.000-04:00

For a quick way to create a hyperlink in an Excel worksheet, you can use the mouse.

With this drag and drop technique, use the right button on the mouse to drag a cell to a different location.

When you stop dragging, as shown in this Excel video tutorial, use the popup menu command to insert a hyperlink.

This is an efficient way to create a table of contents in an Excel workbook, or to make a quick link from one sheet to another. These hyperlinks will make it easy to navigate through your Excel workbooks.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

How to Create Excel 2007 Pivot Table

2011-07-19T00:01:00.000-04:00

Here are the steps for creating a pivot table in Excel 2007. The source data contains information about insurance policies, and with a pivot table, it is easy to summarize in different ways.

With a few clicks, the thousands of rows of data can be totaled by Region, without using any formulas. Then, another couple of clicks, and the total value for different locations are shown.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Excel Paste As Values With Mouse

2011-07-18T21:09:00.000-04:00

Instead of using the Excel Ribbon commands to copy cells, and paste as values, you can use a quick and easy mouse shortcut.

As shown in this Excel video tutorial, here are the steps:

Select the range that you want to copy.
Then, point to the border of the selected range, and press the RIGHT mouse button, as you drag slightly to the right.
Still pressing the mouse button, drag the selection back to its original position, and release the mouse button.
In the popup menu, click Copy Here as Values Only. That’s it!

Watch this video tutorial, to see this quick paste as values technique. And you can read more Excel data entry tips too.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

How to Build an Excel Waterfall Chart

2011-07-10T19:52:00.002-04:00

Watch the Waterfall Chart Utility Video

In the following video, you can see how a complex Excel waterfall chart is set up, and how the waterfall chart add-in makes the task easier.

You can see how a simple Excel waterfall chart is build, and the formulas that are required, in the following video tutorial. The step by step instructions are shown below the video.

Instructions for Building an Excel Waterfall Chart

With an Excel waterfall chart, you can show a starting price, the rise and fall as other values are applied, and a final price.

Here is the original data, with a starting value, and a change in value for each month.

To prepare the data for the Excel waterfall chart, 5 blank columns are added, between the month names, and the values.

The headings, Base, End, Down, Up and Start are added to the blank columns.

Enter the Formulas

The Base column will not be visible in the completed chart, but this series provides a starting point for the Up and Down bars.

In the Base column, the following formulas are added.

B2: =“ “
B4: =SUM(B3,E3:F3)-D4
Copy B4 formula down to B15
B17: = “ “

In column F, the Start value is shown. It is the first visible bar in the chart

Start Formula:

F3: =G3

Next, the End value is calculated in column C, using this formula.

C16: =SUM(B15,E15:F15)-D16

In the Down column, formulas calculate the result for data that is a negative amount.

D4: = - MIN(G4,0)
Copy formula down to D15

The final set of formulas is in the Up column, using this formula:

E4: = MAX(G4,0)
Copy formula down to E15

Create the Excel Waterfall Chart

After the data is set up, select all the data and heading, but do not include the original data, in column G.

Insert a 2-D Column chart, using the Stacked Column chart.

To make the Base series invisible, right-click one of the Base columns, and format the series with no fill and no borders.

Next, right-click one of the Down columns, and format that series with red fill.

Then, right-click an Up column, and format that series with Green fill color.

Then, format the End and Start bars with gray fill color.

To make the columns closer together, right-click on any column, then click Format Data Series.

Reduce the Gap width to about 10%, so the columns are close together.

The Completed Excel Waterfall Chart

To tidy up the waterfall chart, you should remove the Legend, which isn't useful in this chart type.

The Excel waterfall chart is finished!

The Waterfall Chart Utility

If you don't have enough time or patience to build a waterfall chart and its formulas, you can quickly create the chart by using Jon Peltier's waterfall chart utility.

This Excel add-in is easy to use, and can work with complex data, such as data that drops below the zero line. The waterfall chart utility also adds join lines, to connect the tops or bottoms of the columns.

The add-in is quick and easy to install, and creates a command on the Excel Ribbon. After you enter your labels and original values, just click the Waterfall Plot command to create the chart.

_______________

Excel Waterfall Chart for Pocket Price

2011-07-08T21:21:00.001-04:00

With a waterfall chart in Excel you can see a list price, then discounts, and an invoice price. Off-invoice discounts can also be shown, and then the pocket price and pocket margin.

This video shows a couple of waterfall charts, and the table setup that is used to create the different columns and lines in the chart.

To save time and effort, you can use the PeltierTech Excel waterfall chart utility, which creates the formulas and chart in just a couple of clicks.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.
_________________

Create a Simple Timeline Gantt Chart in Excel

2011-05-13T13:24:00.002-04:00

For a simple project, you can create a timeline in Excel, by using a stacked Bar Chart, to create a Gantt Chart. For more project management ideas, visit the Project Management Templates Kit page.

Watch this short Excel Video tutorial to see the steps.

Filter Excel Data to a Different Sheet

2010-12-11T21:03:00.001-05:00

Without using a macro, you can automatically copy specific data to a different sheet, by using an Excel Advanced Filter.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.
_________________

Select Sections of an Excel Pivot Table

2010-12-04T20:59:00.001-05:00

Watch this short video, to see the steps for selecting specific sections of an Excel pivot table. For example, select just the headings, or labels and data in a pivot table.

Change the pivot table settings in the Excel Ribbon, to modify the way that selections are made.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.
_________________

Combine Excel MATCH Function With VLOOKUP

2010-11-29T20:52:00.001-05:00

Make an Excel VLOOKUP formula more efficient, by using the MATCH function in the formula, to find the applicable column.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Hide Duplicates in Excel 2007

2010-11-22T20:43:00.001-05:00

A list in Excel might have one or more repeated items. To make the list easier to read, you can hide the second and subsequent duplicates, by using Excel conditional formatting.

This video shows you the steps for hiding the duplicate items.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Change Pivot Table Layout in Excel 2007

2010-11-15T20:36:00.001-05:00

By default, in Excel 2007, the pivot table row fields are all in the same column, in Compact layout. By choosing Outline form or Tabular layout, you can opt to have the row fields in separate columns.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Auto Add New Items to Excel Drop Downs

2010-10-08T21:19:00.000-04:00

In this Excel video tutorial, you'll see how to create an Excel Data Validation drop down list that allows users to add new items. New data validation items will be automatically added to the drop down list, and the list is sorted alphabetically.

For detailed instructions and an explanation of the Excel VBA code, see Excel Data Validation - Add New Items.

_____________

Show Top 10 With Excel Pivot Tables

2010-09-30T21:13:00.000-04:00

With the Top 10 filter feature in a pivot table, you can quickly focus on the best or worst results in your data. With different settings, you can see the highest or lowest items, or the items that make up a specific Percent, or the items that total a set Sum.

For written instructions for this Excel tutorial, see the Excel Pivot Table Filters - Top 10.

_______________

Excel Status Bar Shows Filter Mode for AutoFilter

2010-09-01T20:58:00.000-04:00

After you apply an Excel AutoFilter, sometimes the Status Bar shows "Filter Mode" instead of showing the record count for the filtered data. This Excel video tutorial explains why this problem occurs, and shows you a couple of workaround solutions.

For written details of the problem and workarounds, see Count of Filtered Records in Status Bar

________

Select Multiple Items From Excel Drop Down List

2010-08-15T20:50:00.000-04:00

This data validation technique allows you to select an item from an Excel drop down list, and then select another item, or several items, in the same drop down.

With the slight variations in the Excel macros, you can store all the selected items in one cell, or fill them across the row, or down a column.

For written details, see the Contextures Blog post Select Multiple Items From Excel Data Validation List.

_____________

Count Cells With Excel COUNTIF Function

2010-08-01T00:01:00.000-04:00

You can use the Excel COUNTIF function to count cells that contain a value higher than a set amount.

For example, to count all the students who passed a course, you could test for scores greater than or equal to 50, which is the passing grade.

For written instructions, see Count Values in Excel with Count functions

________________

Add a Set Amount to Numbers in Excel

2010-07-12T01:19:00.000-04:00

Instead of creating a column of formulas, you can quickly add a set amount to numbers in Excel, by using the Paste Special command.

Watch this Excel Video Tutorial to see the easy steps to this technique.

For written instructions please visit the Excel Data Entry Tips page.

____________

Create a Drop Down List in Excel Worksheet

2010-07-11T01:14:00.000-04:00

Instead of allowing users to type whatever they'd like in a worksheet cell, you can create a drop down list, and users can select an item from the list. The easy steps are described in this Excel video tutorial.

By creating a drop down list, you'll ensure that only valid items are entered in your Excel worksheet, and it will make data entry easier for the worksheet's users.

For detailed instructions, see Data Validation Basics

______________

Troubleshoot Missing Excel Data Validation Arrows

2010-06-29T01:20:00.000-04:00

In this Excel video tutorial, you'll see how to troubleshoot for a solution to missing arrows for Excel Data Validation drop down lists.

For written instructions, please see Missing Data Validation Arrows

___________

Organize Your Data in an Excel 2007 Table

2010-06-15T01:14:00.000-04:00

There's a new feature in Excel 2007 -- the Excel Table -- that you can use to convert a list of data into a formatted Excel Table.

Tables have many helpful features, such as sorting and filtering, that will help you organize and view your data.

An Excel Table makes an excellent source for a pivot table, and I recommend that you use this feature if you plan to create a Pivot Table from the data.

For written instructions, please see Creating an Excel Table

____________

Create Running Totals in Excel Pivot Table

2010-05-26T00:42:00.000-04:00

With running totals you can show accumulated sales for the year, over several months. Use the Custom Calculation feature in Excel's pivot tables.

For written instructions, please see Excel Pivot Table Tutorial Running Totals.

Compare Annual Data in Excel Cluster Stack Chart

2010-05-13T21:44:00.002-04:00

Annual data is easy to compare with a column chart that has both clustered and stacked column chart. This video shows the Excel cluster stack chart add-in that makes it easy to create these complex charts, with just a few clicks.

You can follow instructions to create this type of clustered stacked column chart by arranging your Excel data, or use the add-in.
Note: I received a complimentary copy of this add-in.
________

Create a UserForm in Excel - Part 3

2010-04-27T00:59:00.000-04:00

In this Excel video tutorial, which is Part 3 of 3, you'll learn how to add Excel VBA code to the controls, and you'll see how to test the Excel UserForm.

The Excel VBA code runs when a specific event occurs, such as clicking a button, or entering a combo box. In this example, the user will click a button, and the Excel VBA code will move the data to the worksheet storage area.

For written instructions for this Excel tutorial, see: Create a UserForm

_____________

Create a UserForm in Excel - Part 2

2010-04-20T00:57:00.000-04:00

In this Excel video tutorial (part 2 of 3), you'll learn how to add buttons and a title on an Excel UserForm.

With buttons on the Excel UserForm, a user can click to make something happen. For example, click a button after entering data in the text boxes, when you're ready to move the data to the worksheet storage area.

For written instructions for this Excel tutorial, see: Create a UserForm

_______________

Create a UserForm in Excel - Part 1

2010-04-13T00:54:00.000-04:00

With an Excel UserForm, you can make it easier for users to enter data in a workbook.

In this Excel video tutorial (Part 1 of 3), you'll see how to create a blank Excel Userform. Then you'll name the Excel UserForm, and next you'll add text boxes and labels.

Users will be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box.

For written instructions for this Excel tutorial, see: Create a UserForm

_____________

Create Excel Worksheet Graphs With Data Bars

2010-04-09T19:27:00.001-04:00

Instead of creating an Excel chart, you can use Excel conditional formatting data bars in the worksheet cells. This feature creates a miniature chart in each selected cell, for a quick picture of the data.

For detailed instructions for this Excel tutorial, see: Excel Conditional Formatting - Data Bars

___________

Prevent Old Items in Excel 2007 Pivot Table Drop Downs

2010-03-29T19:11:00.000-04:00

Change a setting in the Excel 2007 pivot table options, to remove any old items in the pivot table drop downs.

For example, if you stop selling a product, you don't want to see its name in the Product drop down. If a sales rep leaves the company, the Sales Rep drop down should no longer contain that name.

Watch this Excel video tutorial to see the steps for changing the pivot table option.

For written instructions see Remove Old Items in Excel 2007 Pivot Table

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Highlight a Row in Excel with Conditional Formatting

2010-03-22T19:07:00.000-04:00

You can highlight all the data in a row, if a cell in one column has a specific value. For example, color the cells if the date is more than one month ago, or if the total is greater than $3000.

In this video, a row will be colored if the number of units sold is greater than 75. Watch the video to see the steps for creating the conditional formatting.

For written instructions, see Apply Conditional Formatting to a Row in Excel

_________________

P.S. There are more Excel tutorials and videos on my Contextures website.

_________________

Copying Excel VBA to Different Workbook

2010-03-03T00:17:00.000-05:00

If you find Excel VBA code in a sample workbook online, you might like to add it to one of your workbooks. You can copy all the code in a VBA code module with an easy technique.

By using the steps shown in this short Excel video tutorial, you can quickly copy Excel VBA code from one workbook to another.

_________________

P.S. There are more Excel VBA tips on my Contextures website.

_________________

Adding Excel Pivot Table Report Filters

2010-02-26T17:36:00.000-05:00

An Excel pivot table is a great way to summarize your data, and it is quick and easy to create.

Instead of viewing the results for all the data, you can use pivot table Report Filters to focus on specific areas of the data.

For example, look at the sales for one city, or filter for one salesperson’s orders.

This short video shows you the steps for adding Excel pivot table Report Filters, filtering for one or more pivot items, and clearing the filters when you want to see all the data again.

Please visit the Contextures website for more details on Excel Pivot Table Report Filters.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Types of Excel Drop Down Lists

2010-02-20T13:49:00.001-05:00

You might be familiar with data validation drop down lists in Excel. They make it easy for users to quickly and easily enter data in an Excel worksheet.

However, they have a few drawbacks, such as a small font size, that can’t be changed. Also, the number of visible items is limited to 8 items, so it can be inconvenient to scroll through a long list of items.

Instead of data validation drop down lists, you could use one of the Excel combo box options.

The first type is a Form Control combo box, and you can change the number of rows in that drop down list.
The second type is an ActiveX combo box, where you can change both the font size and number of rows.

This short video gives you an overview of the different types of Excel drop down lists, along with their advantages and disadvantages.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Remove Duplicates With Filter in Excel 2007

2010-02-10T17:59:00.000-05:00

In Excel 2007, you can use an Advanced Filter to create a list with no duplicates. This will leave the original list unchanged, and creates a new list with the unique items.

For written details see Filter Unique Records

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Find a Product Price with Excel VLOOKUP

2010-01-19T23:27:00.000-05:00

If you have a product pricing table in Excel, you can use a VLOOKUP formula to look up the prices from other cells in the workbook.

In this video, you'll see how to use a VLOOKUP function to find the price for a specific product. The VLOOKUP function looks in the first column of the table, to find the selected product name. Then, it returns the product price, from the column number that was entered in the VLOOKUP formula.

For written instructions for this Excel tutorial, see: VLOOKUP Function

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Enter Dates and Times in Excel

2010-01-05T22:10:00.000-05:00

Use formulas for automatically updating dates and times, or enter static dates and times with keyboard shortcuts.

To enter a date that will automatically update to the current date and time when you open the file, or when you calculate the worksheet, use this formula: =TODAY()

To enter a date and time that will automatically update to the current date and time when you open the file, or when you calculate the worksheet, use this formula: =NOW()

To enter the current date, that will remain static, use the keyboard shortcut: Ctrl + ;

To enter the current time, that will remain static, use the keyboard shortcut: Ctrl + Shift +;

_________________

P.S. There are more Excel data entry tips on my Contextures website.

_________________

Customize the Excel 2007 Status Bar

2010-01-04T22:02:00.000-05:00

The Excel 2007 Status Bar shows summary amounts for selected numbers, and contains other information, such as the status of the Caps and Num Lock keys.

You can add or remove these features, by right-clicking on the status bar.

This Excel Quick Tips video shows you the steps for changing the items in the Excel 2007 Status Bar.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Change the Number of Sheets in New Excel Files

2010-01-03T21:56:00.000-05:00

When you start using Excel, all new workbooks have 3 blank sheets. You can change the default number of blank sheets in a new workbook, to suit your requirements.

For example, if you usually use only one worksheet, you can limit the number of worksheets to one. If you usually add more blank sheets to a workbook, you can set the default number higher.

This Excel quick tips video shows you the simple steps to changing the setting.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Create Quick Excel List of Dates by Week

2010-01-02T21:48:00.000-05:00

You can use an Excel mouse shortcut to quickly create a list of dates in weekly intervals.

Type the starting date on the worksheet. Then, select that cell and point to the Fill Handle at the bottom right corner of the cell.
Press the right mouse button and drag down.
Release the mouse button, and click on Series
For the Step value, type 7, which is the number of days that you want between the dates.
Click OK, to complete the series.

_________________

P.S. There are more Excel data entry tips on my Contextures website.

_________________

Print All Sheets in an Excel Workbook

2010-01-01T20:07:00.000-05:00

If you want to print all the worksheets in an Excel workbook, you don’t have to print each worksheet individually.

Instead, you can select all the worksheets, and the click Print or Print Preview, to see all the sheets.

This very short Excel Quick Tips video shows you the easy steps.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Rearrange Data in Excel

2009-12-30T12:47:00.000-05:00

If data is arranged horizontally in Excel, you can quickly change it to a vertical list. For example, in the video below, the months are listed horizontally across the worksheet, in row 3.

With the Transpose option in the Paste Special command, you can change the months to a vertical list, in a single column.

There are written instructions to transposing data in Excel.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Format a Table in Excel 2007

2009-12-29T21:05:00.000-05:00

If you have a large list of data in Excel 2007, format the data as an Excel Table to make sorting and filtering easier, or to use the table as the source for a Pivot Table.

In this Excel Quick Tips Video, you can see the simple steps for creating a formatted Excel Table from a list in Excel 2007.

_________________

P.S. There are written instructions for creating Formatted Tables in Excel 2007.

_________________

Move to a Range Border With Excel Shortcut

2009-12-28T14:43:00.001-05:00

To save time when working in a large Excel table, you can use Excel mouse shortcuts or Excel keyboard shortcuts, to move to the borders of the range.

With this technique, you can quickly go from the top to the bottom of a range, even it there are thousands of rows in the table.

This Excel Quick Tips video shows you the Excel mouse shortcut for moving to the range border.

_________________

P.S. There are more Excel tips and tutorials on my Contextures website.

_________________

Create a Quick Pivot Table in Excel 2007

2009-12-26T20:46:00.000-05:00

In Excel, you can use a pivot table as a powerful data analysis tool.

It only takes a few seconds to create a pivot table in Excel 2007. In this Excel Quick Tips video, you can see how easy and quick it is to create a blank pivot table, and add a couple of fields.

The steps are:

Select a cell in an Excel table of sales data,
Click a button on the Ribbon,
Click OK in the pivot table dialog box,
Check boxes in the Pivot Table field list, to add a couple of fields to the blank pivot table.

_________________

P.S. For written instructions, see create a pivot table in Excel 2007.

_________________

Quick Grand Totals in Excel

2009-12-24T10:35:00.000-05:00

Instead of entering each SUM function individually, you can use the AutoSum feature to create all the grand totals with one click.

Watch this short Excel video tutorial to see the steps for creating Grand Totals in Excel with one click.

___________

P.S.: For more Excel SUM tips visit the Contexture Excel SUM Functions page.

________________

Create a Quick Number Series in Excel

2009-12-23T18:58:00.000-05:00

Instead of typing a long list of numbers, you can automatically create a series of numbers in Excel, such as 2, 4, 6, 8, by using the AutoFill feature.

Type the first two numbers in the series, and then drag the Fill Handle to continue the series on the worksheet.

This Excel Quick Tips video shows you how to use the AutoFill feature to fill a series.

_____________

P.S. There are more Excel Data Entry tips and shortcuts that will help you.

_____________

Excel 2007: Print Comments

2009-12-22T18:56:00.000-05:00

If you print an Excel 2007 worksheet, you can also print all the comments that were inserted in the worksheet.

This Excel Quick Tips video shows you how to show the comments, and then print the sheet with the comments displayed.

_____________

P.S. There are details and examples on Printing Comments in Excel 2003.

____________

Lock Worksheet Titles in Excel 2007

2009-12-21T16:22:00.000-05:00

To keep the top rows of your worksheet in view at all times, you can lock them in place with the Freeze Panes feature in Excel 2007.

This Excel Quick Tips Video shows you the steps for selecting the top section of the worksheet that you want to freeze, and locking the rows.

___________________

P.S. For the steps to Freeze Title Rows in Excel 2003, see the Excel FAQs.

____________

Easily Name Selected Cells in Excel

2009-12-20T10:28:00.000-05:00

You can use labels on the worksheet to create names for adjacent cells.

For example, if cell D3 contains the text “TaxRate1” and cell E3 contains a tax rate percentage, Excel can use the label in D3 as the name for cell E3.

Watch this very short video to see the steps.

___________

P.S.: For more Excel tips visit the Contexture Excel Tips website.

________________

Show Message When Excel Cell is Selected

2009-12-19T20:38:00.000-05:00

On a data entry sheet in Excel, it should be easy for users to find the cells where they should input text or numbers. The text labels beside the data entry cells usually make it clear what data should be entered.

However, sometimes an additional note makes things clearer. Instead of just having a text label that says “Enter Date”, you can display a popup message when someone clicks on the data entry cell.

The popup messages are created by using the data validation feature. It has settings to show data entry instructions, or other messages, when someone clicks on a cell.

_________________

P.S. There are written instruction for Data Validation Messages.

_________________

Choose a Random Name in Excel

2009-12-18T10:19:00.000-05:00

If you have a list of names, and need to select one name from the list, you can use the RAND function.

Enter the RAND function in the adjacent column. For example, in cell C2 type: =RAND()

Then, copy the RAND formula down to the last row of data.

Next, select one cell in the RAND column, and sort the data.

The name at the top is the winner.

___________

P.S.: For more Excel tips visit the Contexture Excel Tips website.

________________

Select a Large Range in Excel

2009-12-17T20:29:00.000-05:00

It can be difficult to select a large range of data in Excel. As you scroll down the worksheet, the speed increases, and it’s tough to find the row where you want to stop selecting.

Instead of dragging down a long list of data, use this technique:

Click the first cell in the range that you want to select.
Then, scroll down, so you can see the last cell that you want to select.
Press the Shift key, and click on the last cell that you want to select.

This Excel Quick Tips video shows you the steps.

_________________

P.S. For more keyboard shortcuts and tips, see Excel Keyboard Shortcuts.

_________________

Convert Excel Formulas to Values

2009-12-15T13:34:00.001-05:00

Sometimes you create formulas in Excel, and calculate the results, and then you want to lock in those results, so they won’t change later.
You can use this mouse shortcut to copy and paste the formula results as values, in one quick step.
In this example, a column with results from the RAND function is quickly changed to values, by using a mouse shortcut.

_________________

P.S. There are more Excel keyboard on my Contextures website.
_________________

Change Excel Hyperlinks to Text

2009-12-08T20:25:00.000-05:00

If you enter a website’s URL or email address in Excel, a hyperlink might be automatically created. That’s fine in some cases, but sometimes you might want plain text, instead of hyperlinks.

To change the hyperlinks to normal text, you can copy the hyperlinked addresses, and paste them as values.

This Excel Quick Tips Video shows you the steps.

_________________

P.S. For more ways to convert Excel hyperlinks to text, see Prevent Hyperlinks, in the Excel FAQs.

_________________

Create a Quick List of Dates in Excel

2009-12-05T14:00:00.000-05:00

In all versions of Excel, you can use the AutoFill handle to create a sequential list of dates, or create a list with a single date.

This Excel quick tips video shows you the simple mouse shortcut to create the lists.

_________________

P.S. See more Excel Data Entry Tips.

_________________

Make Numbers Jump Out in Excel

2009-11-23T20:06:00.001-05:00

In an Excel workbook full of numbers, important information can get lost. To make the problems easier to spot, you can add Excel conditional formatting to the worksheet.

In this short video, you can see the steps for adding conditional formatting to a group of cells.

Excel Dashboards

___________

P.S.: To see another example of Excel conditional formatting, please visit this page: Excel Conditional Formatting Based on another cell

________________

Split a Window In Excel

2009-10-30T20:02:00.000-04:00

To see different sections of an Excel worksheet, you can split the window. Then, use the scroll bars to see one section of the worksheet in the top split, and another section in the bottom of the split window.

This video clip shows how to drag the Split Bar, to quickly split the window.

________________

Copy Excel Code to Your Workbook

2009-10-23T23:45:00.003-04:00

If you copy Excel VBA code from a sample file or a website, you can copy it to your own Excel file.

This short Excel video tutorial shows you the steps.

P.S. For detailed instructions please read the article Excel VBA – Adding Code to a Workbook

For more Excel video tutorials, please see the Excel Theatre Blog.
______________

Delete Duplicates in Excel 2003

2009-10-19T00:02:00.002-04:00

If there are duplicate items in a list in Excel 2003, you can remove them by using an Advanced Filter. This Excel video tutorial shows you the steps.

Excel Dashboards

P.S. For detailed instructions, please visit Remove Duplicates in Excel 2003 on the Contextures website.

For more Excel video tutorials, please see the Excel Theatre Blog.
______________

Increase Excel Numbers By Set Amount

2009-10-15T19:59:00.002-04:00

Quickly change numbers in Excel. Add, subtract, multiply or divide, by a set amount or percentage. No Formula required.

P.S. Please visit the Contextures website for more Excel data entry tips.

For more Excel video tutorials, please see the Excel Theatre Blog.
______________

GetPivotData Formula Appears When Linking to Pivot Table

2009-09-22T00:03:00.005-04:00

In Excel, you might want to link to data in a pivot table. However, if you type an equal sign, then click on a cell in a pivot table, you may see a GETPIVOTDATA formula, instead of a simple cell reference.
You can turn this feature off, by following the instructions in this short video.

P.S.: For written instructions, please see Turn Off GetPivotData Formulas for Excel Pivot Tables.

For more Excel video tutorials, please see the Excel Theatre Blog.
______________

Fix Excel Numbers That Do Not Add Correctly

2009-09-14T00:02:00.004-04:00

When you paste data from another program, Excel may treat the numbers as text, and totals that use these numbers will be incorrect.
This video shows a simple way to convert the text "numbers" to real numbers.

P.S.: For written instructions, and other instructions, please visit Convert Text to Numbers

Fill Blank Cells In an Excel Table

2009-09-10T00:52:00.003-04:00

When you import data into Excel, it might have headings in column A, and subheadings in column B, with lots of blank cells under each heading.
Before you can sort or filter that data, you’ll need to fill in the blank cells. This short video shows you a quick way to clean up the data.

Excel Dashboards

Charley Kyd has an awesome collection of Excel dashboards, that will make your reporting tasks easier, and I'm an affiliate for his products:

You can read about my experience with the Excel dashboard kit on my website.

P.S. For written instructions please read the article Fill Blank Cells in Excel to Complete a Table
________________

Remove Enable or Disable Macros Warning

2009-09-08T00:04:00.001-04:00

Sometimes in Excel, when you open a file, a message appears. It asks if you want to "Enable or Disable a Macro”, but you know that there are no macros in the workbook

This can happen if a macro has been added and then removed, leaving an empty module.

Follow the steps in this video, to prevent the warning message from appearing.

P.S.: For written instructions see: Remove Excel macro warning

______________

Name a Group of Cells in Excel

2009-09-05T00:04:00.005-04:00

To make it easier to navigate in Excel, you can select one or more cells, and give them a one word name.

This Excel video tutorial shows you the steps.

Excel Dashboards

P.S.: For a written Excel tutorial on naming ranges, visit the naming ranges in Excel page.

______________

Move the Color Palette in Excel 2003

2009-09-01T00:03:00.003-04:00

While you’re formatting a worksheet in Excel 2003, or earlier versions, you can move the color and border palettes close to the cells you’re formatting.

The steps are shown in this short Excel video tutorial.

P.S.: For written instructions, see the notes on my web site, to move the color and border palettes in Excel.

______

Add a Picture to an Excel Comment

2009-08-29T14:02:00.003-04:00

Instead of text, you can display a picture in an Excel popup comment. This short Excel video tutorial shows you the simple steps required.

P.S.: For written instructions see: Add a picture to a comment
______