IM Consultant Services Call: 513-580-4598 Cincinnati-Based

AN ENGINEER'S APPROACH TO MARKETING

Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

In this tutorial we will cover how to use pivot table to summarize large amounts of data in Excel 2010.

How to Use Pivot Table

In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a “pivot table“) showing the summarized data. A pivot table is also useful for quickly creating unweighted cross tabulations.

Ok… that was the textbook definition.  Basically it is a time saving tool to help summarize a large amount of data with a few clicks of a mouse.

Now it is time to learn how to use pivot table to summarize data.

[jwplayer player=”1″ mediaid=”9442″]

For more Excel tips check out How to Quickly Edit Formulas, How to Select All Cells Matching a Criteria, and How to Convert Text to Numbers.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Excel Tips – How to Convert Text to Numbers in Excel 2010
2. Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010
3. Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010
4. Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Excel Tips - How to Use Pivot Table to Summarize Data in Excel 2010  - Mike Marko

Author: Mike Marko
Click here to contact Mike

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want a system to help you rank in Google just like Bren and I do, then check out Influx Entrepreneur.

Article: Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

Excel Tips – How to Convert Text to Numbers in Excel 2010

Excel Tips – How to Convert Text to Numbers in Excel 2010

In this tutorial we will cover how to both detect text and then convert text to numbers in Excel 2010.

Quick Keyboard Trick to Detect If Cells Are Numeric or Text

A cool trick to detect text in a series of numeric values is using CTRL+SHIFT+# to quickly convert them to dates DD-MMM-YY format.  Only the numeric values will be convert to text, otherwise they will remain unchanged.

How-Convert-Text-To-Numbers-In-Excel-2010Once you have detect if cells are text, there are some tricks you can use to quickly convert text to numbers.

[jwplayer player=”1″ mediaid=”9437″]

How to Convert Text to Numbers

There are several ways to create a checkmark or checkbox in an Excel worksheet. Here are a few of them.

You may find that sometimes after you import or copy data from a database or other external source, the numbers in your worksheet are actually stored in cells as text. This can cause problems with calculations and sorting, among other things.

A telltale sign that a number is stored as text is if the numbers are left-aligned in the column instead of right-aligned.

Error Checking

I’ve always found those small green triangles that sometimes appear in the upper-left corner of cells to be an annoyance and can get in the way of seeing neighboring cell values. These green triangles are actually Error Checking indicators.

Sometimes when numbers are entered into cells that are formatted as Text, these small green triangles appear. Since most times I just ignore them, I didn’t realize that they may contain a simple solution to converting text to numbers.

When you select a cell containing one of these small triangles an error icon appears. Point to the icon and you’ll see a small dropdown arrow. When you click on the arrow, one of the options is Convert to Number. Clicking it converts the numbers formatted as text back to numbers. A simple solution that I’ve always overlooked to convert text to numbers. Now you don’t have to.

Paste Special

Another option for converting text-formatted values to numbers is to multiply each cell by 1 in order to force the conversion to regular numbers. Since you are multiplying the contents of the cells by 1, the numbers still look the same, however, Excel actually replaces the text-based contents of the cell with a numerical equivalent.

To use this technique…

1) In a blank cell type 1 and press Enter;

2) Press CTRL+C to copy the value 1;

3) Select the cells that contain the numbers stored as text that you want to convert;

4) Press CTRL+ALT+V to open the Paste Special dialog;

5) Select the Multiply option and click OK;

6) Go back and delete the contents of the cell where you typed 1 in the first step.

For more Excel tips check out How to Quickly Edit Formulas and How to Select All Cells Matching a Criteria.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010
2. Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010
3. Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010
4. Your Guide on How to Make Money Blogging

Excel Tips - How to Convert Text to Numbers in Excel 2010 - Mike Marko

Author: Mike Marko
Click here to contact Mike

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want a system to help you rank in Google just like Bren and I do, then check out Influx Entrepreneur.

Article: Excel Tips – How to Convert Text to Numbers in Excel 2010

Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

In this tutorial we will cover two tips: how to eliminate divide by zero errors, and also how to add a checkmark, all in Excel 2010.

How to Eliminate Divide by Zero Errors

One question I’m asked is how to get rid of the divide by zero errors, #DIV/0!

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.

In the past we had to use one of these IF formulas to avoid this error.

=IF(D2=0,””,C2/D3) or =IF(D2=0,0,C2/D2)

But starting in Excel 2007, Excel introduced a new Excel function called IFERROR.

With this function you need include your formula only once.

=IFERROR(C2/D2,0) or =IFERROR(C2/D2,””)

You can modify the formula to display either a blank or a zero whenever the formula results in an error.

Keep in mind that, technically, a ‘blank’ or ‘zero’ answer is incorrect. You cannot divide a number by zero. Use this with caution.

[jwplayer player=”1″ mediaid=”9428″]

How to Add Checkmarks

There are several ways to create a checkmark or checkbox in an Excel worksheet. Here are a few of them.

Insert a Symbol

1) From the Insert tab click the Symbol command in the Symbols group;

2) In the Symbol dialog box, on the Symbols tab, click the Font dropdown and select click Wingdings;

3) Scroll down to the bottom of the list and you will find find several check mark symbols. Select the symbol that you want, click the Insert button and then Close.

Using it’s Character Code with Wingdings Font

Change the font of the cells where you want to place the checkbox to Wingdings.

– Hold down the ALT key and type 0252 on the numeric keypad (not the top row of keys).

If you want a matching X…

Create checkmark in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0251 on the numeric keypad.

You can also create the above two symbols in boxes.

Create checkmark in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0254 on the numeric keypad.

Create red x in box in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0253 on the numeric keypad.

Try changing to font color, bolding and increasing the font size.

Add a CheckBox control

You can add selectable Check Box controls on your worksheet which you can check or uncheck by clicking on them.

 

For more Excel tips check out How to Quickly Edit Formulas and How to Select All Cells Matching a Criteria.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010
2. Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010
3. Your Guide on How to Make Money Blogging

Excel Tips - How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010 - Mike Marko

Author: Mike Marko
Click here to contact Mike

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want a system to help you rank in Google just like Bren and I do, then check out Influx Entrepreneur.

Article: Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

In this tutorial we will cover two tips: how to quickly edit formulas, and also how to quickly edit data in multiple cells, all in Excel 2010.

How to Quickly Edit Formulas 

I am sure once you see this tip you will find lots of uses for it.

In typical worksheets, there is a great deal of consistency in formulas. They are usually identical in structure, differing only in the actual cells they are referencing within the spreadsheet. But when it comes to relative referencing, most formulas are identical. For example, =SUM(G1:G50) and SUM(H1:H50) are really SUM(the first row in this column to the cell immediately above this formula).

When you create worksheets that have formulas with identical structures, it makes copying and editing and understanding your formulas a whole lot easier.

To copy a formula, most Excel users type the first formula, then copy and paste it to the other cells. Same goes for editing a formula. Edit the first formula then copy and paste it to the other cells.

Here’s a trick you may not have seen, that can make things a whole lot easier and you can more quickly edit formulas once you get into the habit of using it every day.

Instead of typing or editing the first formula then copying and pasting it to the other cells, try this instead:

1) Select all the cells where you want the formulas to go;2) Type or edit (F2) the formula in the Formula Bar;3) Instead of pressing Enter, hold down the CTRL key and press ENTER. The formula (from Step 1) gets instantly copied to all the other selected cells.

[jwplayer player=”1″ mediaid=”9422″]

How to Quickly Edit Data In Multiple Cells in A Specific Order

Most people routinely input data into cells spread throughout your worksheet in a specific order, such as an invoice or order form. Here’s a  quick and simple method that will enable you to easily input your data  without having to select each cell first, and in any specific order.

Say for example each week you need to input data into cells B8, B9, B10, B11, B12, then over to F12 and F13, then back to A17, D17, etc…. Normally, once you’ve entered data into the cells going down, then you have to click, scroll or use the arrow keys to move to the next input cell in another column or another section of the sheet.

The following is a method you can use each time you need to input data into cells in a specific order. The best thing about this method is that it allows you to select all of the input cells and, as you input information, the active cell moves to the next cell in the order you have predefined.

Define your input cells

1) Determine which cells and in which order you want the data input;

2) While holding down the CTRL key, start by selecting the second input cell first, then the third, then the fourth, etc… and the last cell you select is the first input cell. You now have all your input cells selected (in the order you will enter the data) and your first input cell is now the active cell.

3. With the input cells still selected, click in the Name box (the small rectangular white space immediately above column A and to the left of the Formula Bar), type INPUT and press Enter. You have now named your input cells INPUT.

Select input cells

Now that things are setup, whenever you need to select your input cells click the small dropdown arrow to the right of the Name box and select INPUT. You’ll notice that the active cell is the first input cell. Enter the data in the first cell then press the Enter key or the Tab key to move to the next input cell. If you make a mistake inputting data, simply hold down the Shift key while pressing Enter or Tab to move backwards through the input cells.

Redefine your input cells

If you make a mistake in naming the input cells, or if you subsequently want to change the order of the input cells, you’ll first have to delete the defined name INPUT.

Here’s how…

1. On the Formulas tab click Name Manager in the Defined Names group;

2. Select INPUT, click Delete and OK.

Now you can redefine your input cells as outlined above.

Clear your Input Cells

To quickly clear your input cells, click the small dropdown arrow to the right of the Name box, select INPUT and press the Delete key on your keyboard.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010
2. Three Key Techniques on How to Increase Blog Traffic
2. Your Guide on How to Make Money Blogging

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010  - Mike Marko

Author: Mike Marko
Click here to contact Mike

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want a system to help you rank in Google just like Bren and I do, then check out Influx Entrepreneur.

Article: Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Let’s say you need to select all cells matching a criteria like a specific word, character or value and you want to look at each cell individually… and then possibly edit them. Typically, these cells are scattered all around the worksheet. What’s the best way to locate all of these cells?

Let’s look at an example. Say you want to locate all cells in your spreadsheet that contain the word Rental. Press CTRL+F and type Rental in the ‘Find what’ field. Click the Find All button and all cells containing the word ‘Rental‘ will be listed at the bottom of the ‘Find and Replace‘ dialog.

Now, here’s the best part! Now with the ‘Find and Replace‘ dialog still open, pressing CTRL+A will select all of the items listed at the bottom of the dialog, and it will also select these cells on the worksheet.

At this point, you could close the Find and Replace dialog and all of the ‘found’ cells will still be selected on the worksheet. However, the problem is that once you click any cell in the sheet, all of the ‘found’ cells will be unselected.

Instead, move the ‘Find and Replace’ dialog out of your way by clicking on and dragging its header. You can now continue editing your worksheet while the dialog is still open.

When you need to jump to another of the ‘found’ cells, click its reference in the dialog and it will be selected on the worksheet.

Side Note: you can resize the Find and Replace dialog by dragging any of its edges.

In some situations, I close the dialog and, with all of the ‘found cells’ selected on the worksheet, I immediately use a Fill color to easily identify the cells for later editing.

Three Key Techniques on How to Increase Blog Traffic

Locate and Select All Cells Matching a Criteria Like a Link to Other Workbooks

Another useful application of this ‘Find All‘ trick is to select all cells matching a criteria in a worksheet that have formulas linked to other workbooks.

Formulas that link to other workbooks include the referenced file name surrounded by square brackets []. Doing a ‘Find All‘ for the [ character, will locate all cells with a link to other workbooks and display them in the bottom section of the ‘Find and Replace‘ dialog.

Again, you can either leave the ‘Find and Replace‘ dialog open or you can close it and temporarily apply a Fill color to all the cells.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

 

P.S. – If you like this post, feel free comment down below and/or share on Facebook.

Free Bootcamp Training Mini-Course

Suggested Articles:
1. Three Key Techniques on How to Increase Blog Traffic
2. Your Guide on How to Make Money Blogging
3. How Do You Start a Blog with Kalatu Premium – With Almost No Effort

Excel Tips - How to Select All Cells Matching A Criteria in Excel 2010  - Mike Marko

Author: Mike Marko
Click here to contact Mike

If you are interested in learning how to make money online, click the following links to learn more about the IPAS2, and get the information to form your own opinion from this Empower Network Review, and Jeunesse.

If you want a system to help you rank in Google just like Bren and I do, then check out Influx Entrepreneur.

 

Article: Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

In this tutorial we will cover how to use pivot table to summarize large amounts of data in Excel 2010.

How to Use Pivot Table

In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a “pivot table“) showing the summarized data. A pivot table is also useful for quickly creating unweighted cross tabulations.

Ok… that was the textbook definition.  Basically it is a time saving tool to help summarize a large amount of data with a few clicks of a mouse.

Now it is time to learn how to use pivot table to summarize data.

For more Excel tips check out How to Quickly Edit Formulas, How to Select All Cells Matching a Criteria, and How to Convert Text to Numbers.

 

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: [email protected]

Article: Excel Tips – How to Use Pivot Table to Summarize Data in Excel 2010

Excel Tips – How to Convert Text to Numbers in Excel 2010

Excel Tips – How to Convert Text to Numbers in Excel 2010

In this tutorial we will cover how to both detect text and then convert text to numbers in Excel 2010.

Quick Keyboard Trick to Detect If Cells Are Numeric or Text

A cool trick to detect text in a series of numeric values is using CTRL+SHIFT+# to quickly convert them to dates DD-MMM-YY format.  Only the numeric values will be convert to text, otherwise they will remain unchanged.

How-Convert-Text-To-Numbers-In-Excel-2010Once you have detect if cells are text, there are some tricks you can use to quickly convert text to numbers.

How to Convert Text to Numbers

There are several ways to create a checkmark or checkbox in an Excel worksheet. Here are a few of them.

You may find that sometimes after you import or copy data from a database or other external source, the numbers in your worksheet are actually stored in cells as text. This can cause problems with calculations and sorting, among other things.

A telltale sign that a number is stored as text is if the numbers are left-aligned in the column instead of right-aligned.

Error Checking

I’ve always found those small green triangles that sometimes appear in the upper-left corner of cells to be an annoyance and can get in the way of seeing neighboring cell values. These green triangles are actually Error Checking indicators.

Sometimes when numbers are entered into cells that are formatted as Text, these small green triangles appear. Since most times I just ignore them, I didn’t realize that they may contain a simple solution to converting text to numbers.

When you select a cell containing one of these small triangles an error icon appears. Point to the icon and you’ll see a small dropdown arrow. When you click on the arrow, one of the options is Convert to Number. Clicking it converts the numbers formatted as text back to numbers. A simple solution that I’ve always overlooked to convert text to numbers. Now you don’t have to.

Paste Special

Another option for converting text-formatted values to numbers is to multiply each cell by 1 in order to force the conversion to regular numbers. Since you are multiplying the contents of the cells by 1, the numbers still look the same, however, Excel actually replaces the text-based contents of the cell with a numerical equivalent.

To use this technique…

1) In a blank cell type 1 and press Enter;

2) Press CTRL+C to copy the value 1;

3) Select the cells that contain the numbers stored as text that you want to convert;

4) Press CTRL+ALT+V to open the Paste Special dialog;

5) Select the Multiply option and click OK;

6) Go back and delete the contents of the cell where you typed 1 in the first step.

For more Excel tips check out How to Quickly Edit Formulas and How to Select All Cells Matching a Criteria.

 

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: [email protected]

Article: Excel Tips – How to Convert Text to Numbers in Excel 2010

Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

In this tutorial we will cover two tips: how to eliminate divide by zero errors, and also how to add a checkmark, all in Excel 2010.

How to Eliminate Divide by Zero Errors

One question I’m asked is how to get rid of the divide by zero errors, #DIV/0!

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.

In the past we had to use one of these IF formulas to avoid this error.

=IF(D2=0,””,C2/D3) or =IF(D2=0,0,C2/D2)

But starting in Excel 2007, Excel introduced a new Excel function called IFERROR.

With this function you need include your formula only once.

=IFERROR(C2/D2,0) or =IFERROR(C2/D2,””)

You can modify the formula to display either a blank or a zero whenever the formula results in an error.

Keep in mind that, technically, a ‘blank’ or ‘zero’ answer is incorrect. You cannot divide a number by zero. Use this with caution.

How to Add Checkmarks

There are several ways to create a checkmark or checkbox in an Excel worksheet. Here are a few of them.

Insert a Symbol

1) From the Insert tab click the Symbol command in the Symbols group;

2) In the Symbol dialog box, on the Symbols tab, click the Font dropdown and select click Wingdings;

3) Scroll down to the bottom of the list and you will find find several check mark symbols. Select the symbol that you want, click the Insert button and then Close.

Using it’s Character Code with Wingdings Font

Change the font of the cells where you want to place the checkbox to Wingdings.

– Hold down the ALT key and type 0252 on the numeric keypad (not the top row of keys).

If you want a matching X…

Create checkmark in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0251 on the numeric keypad.

You can also create the above two symbols in boxes.

Create checkmark in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0254 on the numeric keypad.

Create red x in box in Excel 2007, 2010, 2013 – Hold down the ALT key and type 0253 on the numeric keypad.

Try changing to font color, bolding and increasing the font size.

Add a CheckBox control

You can add selectable Check Box controls on your worksheet which you can check or uncheck by clicking on them.

 

For more Excel tips check out How to Quickly Edit Formulas and How to Select All Cells Matching a Criteria.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: [email protected]

Article: Excel Tips – How to Eliminate Divide by Zero Errors and Add Checkmarks in Excel 2010

Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

In this tutorial we will cover two tips: how to quickly edit formulas, and also how to quickly edit data in multiple cells, all in Excel 2010.

How to Quickly Edit Formulas 

I am sure once you see this tip you will find lots of uses for it.

In typical worksheets, there is a great deal of consistency in formulas. They are usually identical in structure, differing only in the actual cells they are referencing within the spreadsheet. But when it comes to relative referencing, most formulas are identical. For example, =SUM(G1:G50) and SUM(H1:H50) are really SUM(the first row in this column to the cell immediately above this formula).

When you create worksheets that have formulas with identical structures, it makes copying and editing and understanding your formulas a whole lot easier.

To copy a formula, most Excel users type the first formula, then copy and paste it to the other cells. Same goes for editing a formula. Edit the first formula then copy and paste it to the other cells.

Here’s a trick you may not have seen, that can make things a whole lot easier and you can more quickly edit formulas once you get into the habit of using it every day.

Instead of typing or editing the first formula then copying and pasting it to the other cells, try this instead:

1) Select all the cells where you want the formulas to go;2) Type or edit (F2) the formula in the Formula Bar;3) Instead of pressing Enter, hold down the CTRL key and press ENTER. The formula (from Step 1) gets instantly copied to all the other selected cells.

How to Quickly Edit Data In Multiple Cells in A Specific Order

Most people routinely input data into cells spread throughout your worksheet in a specific order, such as an invoice or order form. Here’s a  quick and simple method that will enable you to easily input your data  without having to select each cell first, and in any specific order.

Say for example each week you need to input data into cells B8, B9, B10, B11, B12, then over to F12 and F13, then back to A17, D17, etc…. Normally, once you’ve entered data into the cells going down, then you have to click, scroll or use the arrow keys to move to the next input cell in another column or another section of the sheet.

The following is a method you can use each time you need to input data into cells in a specific order. The best thing about this method is that it allows you to select all of the input cells and, as you input information, the active cell moves to the next cell in the order you have predefined.

Define your input cells

1) Determine which cells and in which order you want the data input;

2) While holding down the CTRL key, start by selecting the second input cell first, then the third, then the fourth, etc… and the last cell you select is the first input cell. You now have all your input cells selected (in the order you will enter the data) and your first input cell is now the active cell.

3. With the input cells still selected, click in the Name box (the small rectangular white space immediately above column A and to the left of the Formula Bar), type INPUT and press Enter. You have now named your input cells INPUT.

Select input cells

Now that things are setup, whenever you need to select your input cells click the small dropdown arrow to the right of the Name box and select INPUT. You’ll notice that the active cell is the first input cell. Enter the data in the first cell then press the Enter key or the Tab key to move to the next input cell. If you make a mistake inputting data, simply hold down the Shift key while pressing Enter or Tab to move backwards through the input cells.

Redefine your input cells

If you make a mistake in naming the input cells, or if you subsequently want to change the order of the input cells, you’ll first have to delete the defined name INPUT.

Here’s how…

1. On the Formulas tab click Name Manager in the Defined Names group;

2. Select INPUT, click Delete and OK.

Now you can redefine your input cells as outlined above.

Clear your Input Cells

To quickly clear your input cells, click the small dropdown arrow to the right of the Name box, select INPUT and press the Delete key on your keyboard.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: [email protected]

Article: Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010

Let’s say you need to select all cells matching a criteria like a specific word, character or value and you want to look at each cell individually… and then possibly edit them. Typically, these cells are scattered all around the worksheet. What’s the best way to locate all of these cells?

Let’s look at an example. Say you want to locate all cells in your spreadsheet that contain the word Rental. Press CTRL+F and type Rental in the ‘Find what’ field. Click the Find All button and all cells containing the word ‘Rental‘ will be listed at the bottom of the ‘Find and Replace‘ dialog.

Now, here’s the best part! Now with the ‘Find and Replace‘ dialog still open, pressing CTRL+A will select all of the items listed at the bottom of the dialog, and it will also select these cells on the worksheet.

At this point, you could close the Find and Replace dialog and all of the ‘found’ cells will still be selected on the worksheet. However, the problem is that once you click any cell in the sheet, all of the ‘found’ cells will be unselected.

Instead, move the ‘Find and Replace’ dialog out of your way by clicking on and dragging its header. You can now continue editing your worksheet while the dialog is still open.

When you need to jump to another of the ‘found’ cells, click its reference in the dialog and it will be selected on the worksheet.

Side Note: you can resize the Find and Replace dialog by dragging any of its edges.

In some situations, I close the dialog and, with all of the ‘found cells’ selected on the worksheet, I immediately use a Fill color to easily identify the cells for later editing.

Locate and Select All Cells Matching a Criteria Like a Link to Other Workbooks

Another useful application of this ‘Find All‘ trick is to select all cells matching a criteria in a worksheet that have formulas linked to other workbooks.

Formulas that link to other workbooks include the referenced file name surrounded by square brackets []. Doing a ‘Find All‘ for the [ character, will locate all cells with a link to other workbooks and display them in the bottom section of the ‘Find and Replace‘ dialog.

Again, you can either leave the ‘Find and Replace‘ dialog open or you can close it and temporarily apply a Fill color to all the cells.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Select All Cells Matching A Criteria in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: [email protected]

Article: Excel Tips – How to Select All Cells Matching A Criteria in Excel 2010