MN Excel Consulting - Excel Tips and Tricks

Excluding Some Data from a Chart

2010-01-23T14:19:00.001-08:00

Excel is a whiz at creating charts from your worksheet data. When the program tries to determine what should be included in a chart, it includes all the contiguous data it can find. If you want to exclude some data from the chart, then you need to be very precise in what you tell Excel.

Suppose you keep a month's worth of data in a table and create a chart based on that data. The data was collected from information generated on your company's shop floor. The data includes weekends, but you don't want the weekend data included in the chart. You could hide the rows and they would be excluded from the chart, but you still want the hidden rows to be displayed in the table. In other words, you wants them displayed in the data table, but not in the chart.

There are a couple of ways you could approach this problem. One is to simply make a copy of your data (maybe copy the whole worksheet) and then delete the rows that contain weekend data. You would still have your master data for whatever purposes you need, but you could base your chart on the modified copy of that data.

The drawback to this, of course, is that it creates two sets of data that may need to be updated or kept in sync in some way. It may be better to base your chart on a non-contiguous data range. Assume, for a moment, that your data was in A1:B15, and that there were weekends in rows 7, 8, 14, and 15. You could, within the chart, set the data range for the source data to this:

=Sheet1!$A$1:$B$6,Sheet1!$A$9:$B$13

You could also create a named range that refers to the non-contiguous ranges you want included in the chart. You could then use the named range in your chart, as a reference to the source data.

Finally, if you don't mind adding another column to your data, you could use the new column for your chart source. Assume, for a moment, that your readings are in column A and the dates of those readings are in column B. In each cell of column C you could place the following formula:

=IF(WEEKDAY(B1,2)>5,NA(),A1)

You then end up with a series of readings for all weekdays; the weekends show #N/A for the reading. You can base your chart on this data and Excel will ignore the #N/A values. You can even hide column C so it does not distract from your source data.

Posted courtesy of Tips.Net

Averaging a Non-Contiguous Range

2010-01-23T14:12:00.001-08:00

Figuring out how to average data that is in a contiguous range of cells is easy. When the data is spread over a group of non-contiguous cells then getting the average can be a bit more challenging. Throw into the mix the need to exclude zero values from the average and the solution can be downright elusive.

Sometimes you need to average a series of non-contiguous cells, excluding any cells that may contain zero values. For example, average cells A1, C1, E1, G1, and J1, but only counting those cells that don't contain zero.

Before getting into what works, let's take a look at what doesn't work. First of all, it doesn't work to simply add the cells and divide by 5; that doesn't take zero values into account. Second, it doesn't work to use COUNTIF in the denominator of your formula, as shown here:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

This doesn't work because it examines and counts cells within the entire range of A1:J1, not just the five cells you want considered in the average. You might also think that you could select your five non-contiguous cells, give them a name, and then use the name in your formula. While Excel allows you to create the name, the following gives an error:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

It appears that COUNTIF will only work with a single contiguous range, so the non-contiguous nature of the MyCells range throws the function into a tailspin. A similar problem occurs if you try to use a non-contiguous range with the AVERAGEIF function:

=AVERAGEIF(MyCells, "<>0")

Since you can't use any of the functions you might want to use, you are left to rely on a bit longer formula to calculate the average. You can calculate the average of these five cells by applying a bit of "trickery" to your denominator, in this manner:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

The evaluation done on each cell in the denominator returns either a 1 (for True) or a 0 (for False) depending on whether the cell contains a non-zero value or not. This series of values is added together, providing the necessary count of non-zero cells for the denominator.

Notice that the discussion here has been all about the denominator in the formula, not the numerator. The reason is simple—you can add all five values into the numerator; zero values there don't really matter. The only place they matter is in the denominator, which is what makes calculating this average so tricky.

Posted courtesy of Tips.Net

New Formulas in Excel 2007

2010-01-16T14:52:00.001-08:00

Every new version of Excel seems to introduce a few new tools you can use in your formulas. Excel 2007 is no different; this tip explains what was added and where you can find further information. (This tip works with MS Excel 2007.)

Upgrading to Excel 2007 brings several new worksheet functions in the version of the program. What's needed is a good summary of what those functions are and how they can be used.

There are more than 50 new worksheet functions in Excel 2007. The majority of those were functions that had, in previous versions of the program, been in the Analysis ToolPak. A good, general intro to the new functions can be found in this blog post:
http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx

You might also, in Excel 2007, just use the Help system to find the new functions. Press the F1 key and then click the "What's New?" link. You should see a good overview of what has been added to this version of the program.

If you want to see all the added functions in one list, then the following site provides a good list. (Just scroll down to the "Functions" section of the page.)
http://visio.mvps.org/Excel_2007.htm

Posted courtesy of Tips.Net

Sequential Page Numbers Across Worksheets

2010-01-10T12:45:00.001-08:00

How do you want your page numbers to appear on your printed worksheets? Chances are good that you want them to be sequential, regardless of what you print. Here's how to make sure that the page numbers are, in fact, in sequential order. (This tip works with MS Excel 2007.)

There are two approaches you can use to get the page numbering you want. By default, Excel determines what it feels is the best starting page number when printing a worksheet. If you print just a single worksheet, Excel starts the numbering at page 1. If you print multiple worksheets at the same time (create a selection set of worksheet tabs before you start printing), then Excel prints the worksheets sequentially, one after the other, and numbers the pages sequentially as well.

Thus, to get the sequential page numbers you want, you should either select the worksheets you want to print before issuing a single print command or you should display the Print dialog box and, using the Entire Workbook radio button, specify that you want to print the entire workbook.

The second approach is to specify, manually, what page number you want Excel to use for a beginning page number. This approach works well when you can't print all your worksheets at once or if the worksheets you need to print are in separate workbooks. All you need to do is display the Page Setup dialog box (display the Page Layout tab of the ribbon and click the small icon at the bottom-right of the Page Setup group) and use the First Page Number box to specify what page number you want Excel to use.

One final note: You'll want to make sure that you have the headers or footers of your worksheets set up to actually include page numbers. Excel doesn't print them automatically; you need to specify that the header or footer contain a page number.

Posted courtesy of Tips.Net

Shortcuts to Hide Columns

2010-01-10T12:42:00.001-08:00

Need a quick way to hide and unhide columns in a worksheet? The shortcuts described in this tip can help fill the bill. (This tip works with MS Excel 2007.)

In older versions of Excel, Ctrl+0 would hide selected columns and Ctrl+Shift+0 to unhide those columns. When upgrading to Excel 2007, however, only Ctrl+0 works, not Ctrl+Shift+0. Some people wonder if Microsoft changed one shortcut but not the other.

There was no change in the shortcuts; they both still work just fine in Excel 2007. If the second shortcut (Ctrl+Shift+0) doesn't work on your system, it could be because there is some add-in being loaded that stops the shortcut from working properly or, perhaps, because a custom shortcut key has been defined that uses the same key press.

Another thing to keep in mind is that the only way that Ctrl+Shift+0 works is if you have at least one hidden column selected before you use it. For example, if you hide columns D and E, you can later unhide them by selecting columns C through F (which obviously includes the hidden columns) and then pressing the shortcut.

Finally, you should be aware that both of these shortcuts only work if you use the 0 key on the main keyboard. They won't work if you use the 0 key on the numeric keypad.

Posted courtesy of Tips.Net

An Average that Excludes Zero Values

2010-01-03T13:06:00.001-08:00

Excel allows you to use functions and formulas to analyze your data. One way you can analyze your data is to use the AVERAGE function to find out the average of a range of values; this concept is easy to grasp. What may not be as easy is how you get an average that excludes zero values within the range. (This tip works with MS Excel 2007.)

Many people know how to use the AVERAGE function to determine the average of a range of values. However, some would like to have the average determined based on the non-zero values in the range.

The worksheet function most suited to this purpose is to use AVERAGEIF. You can use it in this manner:

=AVERAGEIF(A1:A50,">0")

This function only includes in the average those cells that contain values greater than zero. If you want to also exclude blank cells, you should use the AVERAGEIFS function. This function differs from AVERAGEIF in that it allows you to specify multiple criteria that indicate which cells to average.

=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")

Of course, if you want to approach the problem "old school" (without using AVERAGEIF or AVERAGEIFS), then there are several ways you can proceed. The first is to remember how an average is calculated. It is defined as the sum of a range of values divided by the number of items in the range. Thus, you could figure the exclusionary average by simply making sure that the denominator (the number you are dividing by) does not include any zero values. For instance:

=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")

This approach uses the COUNTIF function to determine the number of cells in the range (A1:A50) that don't contain zero. If this range contains not only zeros but also blank cells, and you don't want the blank cells figured into the result, then you need to use a more complex formula:

=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)-(COUNTA(A1:A50)-COUNT(A1:A50)))

The COUNTIF function counts cells that do not explicitly evaluate to 0, but it will count blank and text cells. The COUNTBLANK term adjusts for the blank cells and the difference between COUNTA and COUNT adjusts the total count for cells that contain text.

Of course you can also use an array formula to do your calculation:

=AVERAGE(IF(A1:A50<>0,A1:A50))

Remember that array formulas need to be entered by using the combination Ctrl+Shift+Enter. This array formula also excludes blanks or cells containing text.

All in all it is easier to use the AVERAGEIF or AVERAGEIFS functions. When would you not want to use them? When you need to share your workbook with users of Excel versions prior to Excel 2007. (This is when the functions were added to Excel.)

Posted courtesy of Tips.Net

Upgrading Your Personal Workbook

2009-12-07T20:00:00.001-08:00

When you upgrade to Excel 2007, you may wonder if you also have to upgrade to the new format for your Personal workbook. The short answer is that yes, you should upgrade, and there should be no backward compatibility issues in doing so.

The Personal.xls workbook has historically been used to store macros and customizations that you want shared among all the workbooks on the system. Barbara updated to Office 2007, but her company has dictated that files created in Excel 2007 be saved in Excel 2003 format since many of their clients have not yet upgraded. Barbara is wondering if she should continue to use Personal.xls as the personal macro workbook or copy all macros to Personal.xlsb.

Honestly, you can't make that choice in Excel 2007. If you are using the program, all of your macros that previously were stored in Personal.xls should be transferred to your new Personal.xlsb file. Why? Because the Personal.xlsb file is for use on your machine, so there is no issue of backward compatibility for your clients. Workbooks that you save in the older Excel 2003 format will continue to save just fine and be readable by your clients using the older version of Excel.

If, however, you have clients with whom you need to share the macros in your Personal.xlsb file, and they aren't using Excel 2007, then you will need to unhide the workbook and save it in the older format explicitly. It is this older format that you will save with them, and such saving will still not affect the Personal.xlsb file on your system.

Posted courtesy of Tips.Net

Specifying How Excel Interprets Percentages

2009-12-07T19:54:00.001-08:00

When you enter a number into a cell that is formatted for percentages, Excel tries to figure out if there needs to be any adjustment to what you entered. This tip describes the problem and indicates how you can control the "figuring" that Excel does.

When you format a cell to display percentages, Excel assumes that whatever you enter into that cell in the future will be a percentage. Thus, if you enter the number.5, Excel translates the value as 50%. Likewise, if you enter .75, then Excel treats the value as 75%.

A potential problem comes into play, however, when you start to enter numbers greater than or equal to one. For instance, if you put in the number 12, do you mean 12% or 1200%? By default, Excel thinks you mean the latter. Excel includes a control that allows you to specify how you want it to interpret what you enter. If you want Excel to treat the value as 12% instead of 1200%, then you can follow these steps:

1. Click the Office button and then click on Excel Options. Excel displays the Excel Options dialog box.

2. At the left side of the dialog box, click Advanced. (Click here to see a related figure.)

3. Make sure the Enable Automatic Percent Entry check box is selected.

4. Click on OK

Posted courtesy of Tips.Net

Differences between Clearing and Deleting

2009-11-22T16:34:00.001-08:00

When you want to remove information from a worksheet, you can either clear cells or delete cells. This tip examines the difference between the two, focusing on the different ways you can both delete and clear information.

As you are editing your Excel workbooks, one of the most common actions to take is to clear or delete cells. Clearing cells means erasing everything within them, whereas deleting actually deletes the entire cell (or cells). This may sound like an odd distinction, but it is not really. If you compare the cells in your worksheet to paper cups, you can either remove a cup entirely (deleting the cup) or simply remove any contents from the cup (clearing the cup). When you delete a cell, the other cells around that cell move to fill where the cell used to be.

To clear the contents of cells you have selected, display the Home tab of the ribbon and click the Clear tool in the Editing group. When you do, you will see a submenu asking what you want to clear. Your choices allow you to clear any of the following:

· Clear All. Everything related to the cell except the cell itself.

· Clear Formats. Any formatting applied to the cell.

· Clear Contents. The information in the cell. Any formatting and comments remain in place.

· Clear Comments. The comments attached to the cell.

You should make your selection based on what you want cleared and then choose the appropriate menu item. If you like, you can also use the Del key to clear a cell. When you use this shortcut key, it is the same as choosing Contents from the Clear submenu.

Deleting is done by selecting the cells you want to delete and then choosing Delete from the Edit menu. When you do this, Excel attempts to determine how the remaining cells in the worksheet should be moved to close up the hole that will be created by deleting the cells. If it can figure it out (for instance, if you are deleting an entire row or column), then the operation is completed. On the other hand, if it is not obvious how the remaining cells should be moved, Excel displays the Delete dialog box, which allows you to specify whether cells should be moved up or to the left.

Posted courtesy of Tips.Net

What is the quickest way to...

2009-11-16T14:08:00.001-08:00

Copy a formula down the whole column?
Select the cell that contains the formula and double-click the small square
on the bottom, right-hand corner of the cell. (It is called the Fill
Handle. Your cursor turns into a skinny Plus sign when it is positioned
over it.) Your formula will be automatically copied over everything below
it, as far down as there is data. (If there is no data in the same column,
the formula is copied as far down as there is data in the columns next to
it.)

Fill in a series of data?
Enter at least two items in a range of cells. Select those cells as a
group and drag the group's fill handle down to fill the column (or drag to
the right to fill the row). This works with many dates, month names, and
numbers, as well as any custom lists you have set up.

Enter the same value into multiple cells?
Select the range of cells, type the value, and press Ctrl+Enter.

Enter values in a specific range of cells?
If you are in the habit of using the Tab key when you enter data, and you
only want to enter data into one specific range of cells, first select the
cells in which you want to enter data. Each time you press Tab, you will
move to the next cell. In other words, if you select cells A1 through D4,
enter data into A4, and press Tab, your cursor moves to B1, staying within
the selected range.

Posted courtesy of Tips.Net

Using Conditional Formatting to Shade Rows

2009-11-13T07:41:00.001-08:00

If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with conditional formatting.

If you haven't tried out the conditional formatting features of Excel before, they can be quite handy. One way to use this feature is to cause Excel to shade every other row in a table. This is great when you have a particularly wide table, and you want to make it a bit easier to read on printouts. Simply follow these steps:

1. Select the table whose alternate rows you want to shade.

2. Make sure the Home tab of the ribbon is displayed.

3. Click the Conditional Formatting tool. Excel displays a series of choices.

4. Click New Rule. Excel displays the New Formatting Rule dialog box.

5. In the Select a Rule Type area at the top of the dialog box, choose "Use a Formula to Determine Which Cells to Format"

6. In the formula space, enter the following formula:

=MOD(ROW(),2)=0

7. Click on the Format button. Excel displays the Format Cells dialog box.

8. Make sure the Fill tab is selected.

9. Select the color you want used for the row shading.

10. Click on OK to close the Format Cells dialog box.

11. Click on OK to close the New Formatting Rule dialog box.

Those familiar with Excel may wonder why anyone would use conditional formatting to highlight different rows of a table when you can use the table formatting feature (available in the Styles group of the Home tab of the ribbon) to accomplish the same thing. The reason is simple—using conditional formatting provides much more flexibility in the formatting applied as well as in the interval of the rows being shaded.

Posted courtesy of Tips.Net

Printing Different Parts of the Same Worksheet

2009-11-04T17:33:00.000-08:00

Do you have a worksheet from which you need to print only portions of the data available? There are two ways you can control what gets printed—by using a macro or by using custom views.

If you have multiple areas that you print in a worksheet, you may get tired of repeatedly specifying what area you want to print and then printing it. Such a task is well suited to being done with a macro. The macro can take care of specifying a print area and then actually printing the information.

For instance, let's assume that you have two print ranges defined in your worksheet: Range1 and Range2. Further, Range1 should be printed in portrait orientation and Range2 should be printed in landscape orientation. The following macros can be used to print each of the print ranges:

Sub PrintRange1()

ActiveSheet.PageSetup.PrintArea = Range("range1").Address

ActiveSheet.PageSetup.Orientation = xlPortrait

ActiveWindow.SelectedSheets.PrintOut

End Sub

Sub PrintRange2()

ActiveSheet.PageSetup.PrintArea = Range("range2").Address

ActiveSheet.PageSetup.Orientation = xlLandscape

ActiveWindow.SelectedSheets.PrintOut

End Sub

These are very simple macros, but you get the idea—all you need to do is set up the print job in the macro, and then print from the macro itself. You could even attach the macros to toolbar buttons or to a menu option.

If you prefer to not use macros, you could also use the custom views feature of Excel. Simply set the print area, orientation, margins, and other settings desired. Then define this as a custom view. To define a custom view, follow these steps:

1. Make sure the View tab of the ribbon is displayed.

2. In the Workbook Views group, click Custom Views. Excel displays the Custom Views dialog box. (Click here to see a related figure.)

3. Click on Add. Excel displays the Add View dialog box. (Click here to see a related figure.)

4. Enter a descriptive name for the view you are defining.

5. Make sure the Print Settings check box is selected.

6. Click OK.

You can continue to define and save additional views, as desired. Your custom views are saved with your workbook, and you can later use them to print what you want. (Just display the custom view and then print your worksheet.)

Posted courtesy of Tips.Net

Changing a Link's Source

2009-11-02T09:06:00.001-08:00

If your worksheet is linked to data in other worksheets, you may need to change the link from time to time. Here's how to do it:

You may already know that Excel allows you to establish links between your workbook and other objects, such as graphics, Word documents, and other items. (regular links, not hyperlinks.) There may come a time when you wish to change the links. For instance, you may have a picture linked in your workbook, and you want to change the location of the picture (to a different directory or drive). Excel allows you to change your link so it can point to the proper file as the source of the object in the workbook.

To change links in this manner, follow these steps:

1. Click the Office button, then click Prepare | Edit Links to Files. Excel displays the Edit Links dialog box.

2. Select the link you want to change.

3. Click on Change Source. Excel displays the Change Source dialog box.

4. Use the controls in the dialog box to locate and select the file that will serve as the new source of the link.

5. Click Open. Excel updates the link to reflect the new source you specified.

6. Make any additional link changes necessary by repeating steps 2 through 5.

7. Click on OK.

I hope this is helpful! Let me know if you have questions on Excel or any other MS Office product!

Posted courtesy of Tips.Net

Adding Weighted Averages to a PivotTable

2009-10-30T18:22:00.001-07:00

PivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple aggregations, such as sums, counts, and averages. They can't do weighted averages, however. That doesn't mean you are out of luck; you can still calculate weighted averages using the ideas in this tip.

A good example of how to use calculated fields is for summarizing data differently than you can normally summarize it with a PivotTable. When you create a PivotTable, you can use several different functions to summarize the data that is displayed. For instance, you can create an average of data in a particular field. What if you want to create a weighted average? Excel doesn't provide a function that automatically allows you to do this.

When you have special needs for summations—like weighted averages—the easiest way to achieve your goal is to add an additional column in the source data as an intermediate calculation, and then add a calculated field to the actual PivotTable.

For example, you could add a "WeightedValue" column to your source data. The formula in the column should multiply the weight times the value to be weighted. This means that if your weight is in column C and your value to be weighted is in column D, your formula in the WeightedValue column would simply be like =C2*D2. This formula will be copied down the entire column for all the rows of the data.

You are now ready to create your PivotTable, which you should do as normal with one exception: you need to create a Calculated Field. Follow these steps:

1. Select any of the cells in the PivotTable.

2. Make sure the Options tab of the Ribbon is displayed.

3. In the Tools group, click the Formulas tool and then choose Calculated Field. Excel displays the Insert Calculated Field dialog box, shown earlier in Figure 20.

4. In the Name box, enter a name for your new field.

5. In the Formula box, enter the formula you want used for your weighted average, such as =WeightedValue/Weight. (You use field names in the formula; you can select them from the field list at the bottom of the Insert Calculated Field dialog box.)

6. Click OK.

Your calculated field is now inserted, and you can use the regular summation functions to display a sum of the calculated field; this is your weighted average.

Since there are many different ways that weighted averages can be calculated, it should go without saying that you can modify the formulas and steps presented here to reflect exactly what you need done with your data.

Have questions on this or any other Excel topic? Let us know! We hope this helps you become more efficient!

Posted courtesy of Tips.Net

How Does Excel Evaluate Formulas?

2009-10-25T17:29:00.001-07:00

Ever wonder how Excel is evaluating a particular formula? It's easy to figure out if you use the Evaluate Formula tool…

It is often frustrating to figure out exactly how Excel arrives at a particular result—particularly if the formula returning the result is complex. Excel provides a very useful tool you can use to help determine out what is going on when Excel evaluates a formula.

To access this tool, display the Formulas tab of the ribbon. In the Formula Auditing group click the Evaluate Formula tool. Excel displays the Evaluate Formula dialog box.

At this point, Excel shows the full formula from the cell, and part of it is underlined. This underlined area represents the part of the formula that Excel will next evaluate. This allows you to see what intermediate steps Excel follows in arriving at a result. Every time you click the Evaluate button, Excel replaces the underlined portion of the formula with a result.

Nothing you do with the formula evaluator actually affects the formula in your worksheet; it remains unchanged. Instead, Excel simply shows you what happens as it works through each part of the formula to arrive at a result. When you are done using the formula evaluator, click the Close button.

Have questions on this or any other Excel topic? Let us know! We hope this helps you become more efficient!

Using the Keyboard to Change Cell Width and Height

2009-10-19T14:27:00.001-07:00

Some people ask if there is a way to adjust the height and width of a cell by using the keyboard, without using the mouse. (Some people are really adverse to using the mouse!) The answer is yes, there is a way. The problem, however, is that it isn't a terribly intuitive way...

Here's how you do set the row height:

Press Alt. This kicks Excel into a "shortcut key mode" and you should see the shortcut keys appear above each tab of the ribbon.
Press H to indicate you want to use the Home tab. New shortcut keys appear above each tool in the tab.
Press O to indicate you want to use the Format tool in the Cells group. Excel displays a drop-down list of options.
Press H to choose the Row Height option. Excel displays the Row Height dialog box.
Type the value you want for the row height.
Press Enter.

The only difference in these steps when you want to specify the column width is that you should press W in step 4.

Here's another of changing the row height:

Press Shift+Spacebar. Excel selects the entire row.
Press Shift+F10 to display a Context menu. (This is the same menu you see if you were to right-click on the selection.)
Press R to indicate you want to change the row height. Excel displays the Row Height dialog box.
Type the value you want for the row height.
Press Enter.

A similar sequence will work for setting the column width:

Press Ctrl+Spacebar. Excel selects the entire column.
Press Shift+F10 to display a Context menu. (This is the same menu you see if you were to right-click on the selection.)
Press C twice to choose the second "C" command in the Context menu (Column Width).
Press Enter. Excel displays the Column Width dialog box.
Type the value you want for the column width.
Press Enter.

As always, please comment and/or contact me if you would like additional information on how we can help you Build Efficiency in your small business!

Pivot Tables - One of Excel's Most Powerful Tools

2009-10-11T17:56:00.000-07:00

I won't try and hide the fact from you that I am a big fan of Pivot Tables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. to be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about Pivot Tables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If its' not what you expect, simply use the Undo feature and/or have another go! What you must always remember is that you are not changing the structure of your original table in any way at all, so you can do no harm!

What would we use them for?
We would use a Pivot Table to produce meaningful information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a Pivot Table we could very easily and quickly find out:
1. How many people have the same names.
2. How many postcodes are the same.
3. A count of a particular occupation.
4. See only people that match a particular occupation.
5. Find out the addresses of people that match a postcode

In fact the list can go on and on!

What is the advantage ?
Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.

What are macros and why are they useful?

2009-10-11T17:43:00.000-07:00

Macros are a great productivity tool that allows you to run series of actions with a click of a button. Macros help you work efficiently.

What is a macro?
A macro is a set of tasks combined together so that you can run or replay the entire task together with a single command. Macros are a powerful productivity tool. With macros you can perform long or boring tasks just by a single click.

Macros are very popular with most of the Windows software.

Many applications provide macro capability inbuilt, like MS Excel, MS Word etc. These applications provide macro recording capabilities as well as some kind of macro language. For example: you can record a macro in Excel to perform calculations, format cells or just about anything you do in Excel.

What can you do with macros?
Tons. Anything and everything that is repetitive can be done using macros. All of us perform many repetitive actions day after day on our computers. Once you are aware that these are repetitive actions, you can easily create a macro for it and next time perform it with a click of a button.

Examples: Login and check your multiple mail accounts, format data, insert date and copyright notices onto your documents, generate reports, copy data between applications. There are a zillion things you can do.

If you think you are doing the same task again and again and it is frustrating and wasting your time and energy, you are ready to use macros. Even if it is not getting on your nerve, using a macro is a smart and fun way of working. Just understanding what you can do with macros will suggest new and more productive ways to get your job done.

Interested in seeing if a macro will work for your project, send us an email and we’ll review it!

Welcome to the new MN Excel Consulting site and blog!

2009-10-11T17:40:00.000-07:00

After a summer-long redesign, our new site has launched. Follow MN Excel Consulting on Twitter (MNExcel) and become a fan of www.mnexcelconsulting.com on Facebook!

If you would like to see a topic discussed, let me know!