Microsoft Excel might just be the most versatile application ever created. If you are still using Excel just for number crunching, a MS Excel course can open up a whole new world of possibilities. Let me give you just a few examples of some of the offbeat ways, both at work and at home, I have used Excel over the years.
Numerical solution of differential equations
A Microsoft Excel course can introduce you to macros which can allow you to take calculations to the next level-to the point of writing your own statistical functions. However, Excel macros are written in a language that is interpreted rather than compiled (for you non-programmers out there, read that as "slow rather than fast") so complex numerical models seem impossible.
I used to use a complex numerical code to model contaminant transport in groundwater. The calculations were fast, but it was clumsy to get data in and out of the applications. A MS course had turned me on to macros, and I realized that I could call this fast code from a macro by turning it into a DLL. The Excel interface provided an easy way to create scenarios, to view the data, and to tweak on settings to see how the model changed. The compiled code provided fast results that Excel couldn't match. The combination created a powerful and unique tool.
Wine tasting
I've recently become a bit of an oenophile, and I've started a database of wines I've tasted. I track wines by type, country, vineyard, and year. As the information grows, I examine trends. I've noticed my highest rated wines are generally Australian, something I might not have otherwise noticed.
An Excel course will illustrate techniques in sorting and filtering that allows you to pull important kernels of information out of a mass of data.
Computer game statistics
I am an avid computer gamer and Excel has been a vital tool. I can provide complex demographic data on my population in The Sims 2. I can calculate engine efficiency of the different locomotives in Railroad Tycoon 3. In Oblivion I can track quests, merchant prices, and trainers.
I can also list my alchemical ingredients and have it tell me which potions I can make. I can track the changing economy in Sid Meier's Pirates, color coding prices so I can easily see the best ports to sell my plunder. A course in Excel can teach you about features such as conditional formatting or database functions that make these kinds of workbooks possible.
Learn more fun uses from a Microsoft Excel course
Excel is not just about numbers, as these examples are meant to illustrate. The techniques picked up in a MS Excel course can inspire you to use Excel for tasks you may have never considered. There is more to this software than you think, so get ready to explore its full potential in a Excel course.
Monday, January 28, 2008
Friday, January 25, 2008
How to concatenate data easily in excel?
First of all, what the heck is concatenation anyway? We think of Excel as being only able to perform arithmetic functions on data. Wrong. Excel has many features that allow you to work with text data as well.
In Excel, the Concatenate function allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ... text_n )
There can be up to 30 strings that are joined together.
Let me tell you the easiest way to CONCATENATE:Let's take a look at an example:
I have written "alpha" in cell number A1 ane "bet" in cell A2
Based on the Excel spreadsheet description above:
| =Concatenate(A1, A2) | would return "Alphabet" |
| =Concatenate("Tech on the ", "Net") | would return "Tech on the Net" |
| =Concatenate(A1, "bet soup") | would return "Alphabet soup" |
There is another way to use concatenate function: *psst* the hard way:
Lets suppose that cell A1 contains Mary and B1 contains Brown
Another cell, say, C1 contains the concatenation formula: =A1&B1
F132 will display: MaryBrown
The simple definition was to add two or more cells together that contained text. To add to the definition, you can add text cells and/or text strings together.
The next logical question in the MaryBrown case is that you probably want a space to separate the first and last name. In English you are saying add A1 to a specific text character not found in the cells, and then add B1 to that. The specific character just happens to be a space. So here it is:
The new concatenated formula: =A1&" "&B1
The concatenated cell with the space: Mary Brown
Now, let's add the prefix "Ms" in front of Mary's name. Since "Ms" is not in cell, we'll attach the text string in front of the concatenated name.
The completed concatenated formula: ="Ms "&A1&" "&B1
The cell would now display Ms Mary Brown
Note that when concatenating "Ms" that the formula still starts with a equal (=) sign, the text is surrounded by double quotes and the space needed to separate Ms from Mary was included inside the double quotes.
Now, I think the concatenate ghost is running far far away from you =D
How to calculate variance in microsoft excel 2003?
Excel provides useful statistical functions for finding the variance of a data set. In general, these variance functions retrieve a set of values stored in a worksheet range and then make the expected calculation.
A variance, just to remind readers, is a common measure of describing the spread of observations in a distribution. A variance is related to another statistical measure, the standard deviation. A variance is equal to the square of the standard deviation.
Variance of a Sample with the VAR Function
If the data set you're working with is a sample and you do not want to include logical values or text from the set in the calculation, you use the VAR function. For example, if you're using a new production process that is supposed to increase productivity and have a series of data for the numbers of parts produced each day, you can find the sample variance. The VAR function uses the following syntax:
=VAR (data set range)
Variance of a Sample with the VARA Function
If the data set is a sample but you want to include logical values or text in the calculation, you use the VARA function. Excel counts cells containing the logical value TRUE as 1 and cells containing text or FALSE 0. The VARA function uses the following syntax:
=VARA (data set range)
Variance of a Population Sample with the VARP Function
If the data set you're working with is a population and you do not want to include logical values or text from the set in the calculation, you use the VARP function. The VARP function uses the following syntax:
=VARP (data set range)
Variance of a Population with the VARPA Function
If the data set is a population but you want to include logical values or text in the calculation, you use the VARPA function. The VARPA function uses the following syntax:
=VARPA (data set range)
A Final Note About the Data Set Range Argument
One other note: You can include multiple worksheet ranges as your data set range argument when using the variance functions. For example, if you were calculating the variance of a sample using the VAR function and had your data stored in several different worksheet ranges, your VAR function might look like this:
=VAR(B1:B:10,B101:B110,B200)
A variance, just to remind readers, is a common measure of describing the spread of observations in a distribution. A variance is related to another statistical measure, the standard deviation. A variance is equal to the square of the standard deviation.
Variance of a Sample with the VAR Function
If the data set you're working with is a sample and you do not want to include logical values or text from the set in the calculation, you use the VAR function. For example, if you're using a new production process that is supposed to increase productivity and have a series of data for the numbers of parts produced each day, you can find the sample variance. The VAR function uses the following syntax:
=VAR (data set range)
Variance of a Sample with the VARA Function
If the data set is a sample but you want to include logical values or text in the calculation, you use the VARA function. Excel counts cells containing the logical value TRUE as 1 and cells containing text or FALSE 0. The VARA function uses the following syntax:
=VARA (data set range)
Variance of a Population Sample with the VARP Function
If the data set you're working with is a population and you do not want to include logical values or text from the set in the calculation, you use the VARP function. The VARP function uses the following syntax:
=VARP (data set range)
Variance of a Population with the VARPA Function
If the data set is a population but you want to include logical values or text in the calculation, you use the VARPA function. The VARPA function uses the following syntax:
=VARPA (data set range)
A Final Note About the Data Set Range Argument
One other note: You can include multiple worksheet ranges as your data set range argument when using the variance functions. For example, if you were calculating the variance of a sample using the VAR function and had your data stored in several different worksheet ranges, your VAR function might look like this:
=VAR(B1:B:10,B101:B110,B200)
Wednesday, January 23, 2008
How to create charts in microsoft excel 2003?
Excel comes with a built-in chart creator. This wonderful tool allows you to display your data graphically. There are two ways to create a chart in Excel 2003 by using an "instant chart" or by using the Chart Wizard.
To create a chart instantly, highlight the data table, or click and drag the data you want to display in chart form, and press F11. This shortcut will create a columnar chart.
If you would like to create a different kind of chart, you can select the data table or click and drag to highlight the data for the chart, and select the Chart Type button on the Chart toolbar. You can choose your chart type and Excel will create an embedded chart using your selected data.
If you would prefer to use the Chart Wizard to create a chart, select the data you plan to use. Omit grand totals since they will affect the scale of the chart and make it difficult to read.
Click the Chart Wizard button. You will be shown a dialogue box with the first of four steps you'll need to complete to create your chart. Select the chart type you want to use. Click Next.
In Step 2, identify whether your data are in rows or columns, and identify the data range in the field provided, if it is not already there or you want to alter the data range. The Wizard will display a sample chart to show you what your data will look like on the final product. Click Next.
In Step 3, you can add axis labels, a chart title, data labels, legends and a number of other options by selecting the proper tabs in this step. Once you have set these options, click Next. (You can edit this information later via the Chart menu, if desired.)
In Step 4, choose the location of your chart. It can be embedded in the current worksheet or as a new, separate sheet. Click Finish and your chart will appear in the location you've specified
To create a chart instantly, highlight the data table, or click and drag the data you want to display in chart form, and press F11. This shortcut will create a columnar chart.
If you would like to create a different kind of chart, you can select the data table or click and drag to highlight the data for the chart, and select the Chart Type button on the Chart toolbar. You can choose your chart type and Excel will create an embedded chart using your selected data.
If you would prefer to use the Chart Wizard to create a chart, select the data you plan to use. Omit grand totals since they will affect the scale of the chart and make it difficult to read.
Click the Chart Wizard button. You will be shown a dialogue box with the first of four steps you'll need to complete to create your chart. Select the chart type you want to use. Click Next.
In Step 2, identify whether your data are in rows or columns, and identify the data range in the field provided, if it is not already there or you want to alter the data range. The Wizard will display a sample chart to show you what your data will look like on the final product. Click Next.
In Step 3, you can add axis labels, a chart title, data labels, legends and a number of other options by selecting the proper tabs in this step. Once you have set these options, click Next. (You can edit this information later via the Chart menu, if desired.)
In Step 4, choose the location of your chart. It can be embedded in the current worksheet or as a new, separate sheet. Click Finish and your chart will appear in the location you've specified
What is the meaning of charting terms in Microsoft excel 2003 ?
Excel's Chart Wizard and documentation use several charting terms: data markers, data-marker descriptions, legend, chart text, plot area, and chart area.
You'll find it useful to understand just what these words and phrases mean, so the list that follows provides definitions.
Data markers
Data markers are the graphical elements used to represent individual data point values in a chart. In the case of a line chart, for example, Excel uses uses symbols, or points, on a line to show data point values. These symbols or points are the data markers.
Other types of charts in Excel use other data markers. A chart that uses columns or bars, for example, has column or bar data markers. A pie chart has pie-slice data markers, and so on.
Data marker descriptions
Excel typically describes and qualifies data markers using the data-marker descriptions such as axis scales and data labels.
Different types of charts use different data-marker descriptions. Bar, column, and line charts use axis scales. Pie and doughnut charts use data labels.
Legend
A legend names and identifies the data series you've plotted. In the case of a pie chart, for example, the legend typically names the data series and then also shows which colors are used for which pie slices.
In charts that show multiple data series, the legend lists all of the data series and visually shows chart viewers how to identify data series.
Chart text
Chart text predictably describes a chart or some part of a chart. A chart might include a title that shares the chart message such as "Industry Continues to Grow" or a subtitle that clarifies some bit of information about the chart such as "(five-year forecast of domestic revenues)".
Plot area
The plot area of a chart is the area that includes the data markers and data-marker descriptions.
In many charts, the plot area is a rectangle that shows the lines and scales representing the plot area.
In same cases--such as the case of a pie chart or doughnut chart----the circle that shows the slices of pie and the data labels that identify the slices of pie comprise the plot area.
Chart area
The chart area includes plot area, any chart text, and a legend. In other words, the chart area represents the whole enchilada.
You'll find it useful to understand just what these words and phrases mean, so the list that follows provides definitions.
Data markers
Data markers are the graphical elements used to represent individual data point values in a chart. In the case of a line chart, for example, Excel uses uses symbols, or points, on a line to show data point values. These symbols or points are the data markers.
Other types of charts in Excel use other data markers. A chart that uses columns or bars, for example, has column or bar data markers. A pie chart has pie-slice data markers, and so on.
Data marker descriptions
Excel typically describes and qualifies data markers using the data-marker descriptions such as axis scales and data labels.
Different types of charts use different data-marker descriptions. Bar, column, and line charts use axis scales. Pie and doughnut charts use data labels.
Legend
A legend names and identifies the data series you've plotted. In the case of a pie chart, for example, the legend typically names the data series and then also shows which colors are used for which pie slices.
In charts that show multiple data series, the legend lists all of the data series and visually shows chart viewers how to identify data series.
Chart text
Chart text predictably describes a chart or some part of a chart. A chart might include a title that shares the chart message such as "Industry Continues to Grow" or a subtitle that clarifies some bit of information about the chart such as "(five-year forecast of domestic revenues)".
Plot area
The plot area of a chart is the area that includes the data markers and data-marker descriptions.
In many charts, the plot area is a rectangle that shows the lines and scales representing the plot area.
In same cases--such as the case of a pie chart or doughnut chart----the circle that shows the slices of pie and the data labels that identify the slices of pie comprise the plot area.
Chart area
The chart area includes plot area, any chart text, and a legend. In other words, the chart area represents the whole enchilada.
Sunday, January 20, 2008
How to deactivate basic shortcute in microsoft excel 2003?
Over the last few days I have been running basic and advanced Excel courses and one of the common questions that people are asking me is "What is a simple way to Disable a Short cut in Excel?" I have spent lots of time thinking about a non-programmatic way to do this and there is a very simply solution.
Write a macro …
Let us say for example you are writing a Microsoft Excel template that you need various users to enter data into, but you want to limit their ability to use the features of Cut, Copy and Paste. Okay, so let me start off by testing your memory… What is the shortcut keystrokes for Cut, Copy and Paste … I will put you out of your misery if you do not know, they are -
Cut - [Ctrl] + [X]
Copy - [Ctrl] + [C]
Paste - [Ctrl] + [V]
Now to override these basic shortcuts we simply use a macro to do so, keeping in mind that the macro will only disable the short cuts for that document, as long as you choose the right options. Let us go through the process to disable the short cut, Cut.
The first step is to open a new spreadsheet in Microsoft Excel, then go to the Tools menu and then choose Macro from the drop down menu, then select Record New Macro …
The Record Macro dialog box will be visible. In the Macro Name text box, type the text EliminateCut. Now not that I did not have a space between the word Eliminate and Cut. If you have a space, the Excel application will come up with an error because in Microsoft Excel you are NOT allowed to have spaces in macro names.
Second step click in the box that say shortcut key and type in the letter X. In the Store in Macro drop down box the option "This Workbook" should be selected. If it is not, make sure you select it from the drop down box or you will disable Cut for all future workbooks, which will not be a positive outcome. Finally press the OK button.
Word of warning … Do Not Click or do anything on the Worksheet. The Stop Recording toolbar should appear, simply click on the stop button which looks like a stop button on a CD Player and the macro recorder will stop.
Now if you put some text into a cell and deactivate the cell by pressing the [Enter] key, then go back and click on it once and try out your shortcut. What you will notice is that the cell is selected but if you click on another cell and try and paste the value by using [Ctrl] + [V] you will notice that the cell does not move and as such the short cut [Ctrl] + [X] is disabled.
I should note that there are some limitations to this technique. If you actually activate the cell and select the text in the cell itself and then try [Ctrl] + [X] you will find that the text will be cut, but if you use [Ctrl]+[X] on a normally selected cell you will notice [Ctrl] + [X] is disabled.
Write a macro …
Let us say for example you are writing a Microsoft Excel template that you need various users to enter data into, but you want to limit their ability to use the features of Cut, Copy and Paste. Okay, so let me start off by testing your memory… What is the shortcut keystrokes for Cut, Copy and Paste … I will put you out of your misery if you do not know, they are -
Cut - [Ctrl] + [X]
Copy - [Ctrl] + [C]
Paste - [Ctrl] + [V]
Now to override these basic shortcuts we simply use a macro to do so, keeping in mind that the macro will only disable the short cuts for that document, as long as you choose the right options. Let us go through the process to disable the short cut, Cut.
The first step is to open a new spreadsheet in Microsoft Excel, then go to the Tools menu and then choose Macro from the drop down menu, then select Record New Macro …
The Record Macro dialog box will be visible. In the Macro Name text box, type the text EliminateCut. Now not that I did not have a space between the word Eliminate and Cut. If you have a space, the Excel application will come up with an error because in Microsoft Excel you are NOT allowed to have spaces in macro names.
Second step click in the box that say shortcut key and type in the letter X. In the Store in Macro drop down box the option "This Workbook" should be selected. If it is not, make sure you select it from the drop down box or you will disable Cut for all future workbooks, which will not be a positive outcome. Finally press the OK button.
Word of warning … Do Not Click or do anything on the Worksheet. The Stop Recording toolbar should appear, simply click on the stop button which looks like a stop button on a CD Player and the macro recorder will stop.
Now if you put some text into a cell and deactivate the cell by pressing the [Enter] key, then go back and click on it once and try out your shortcut. What you will notice is that the cell is selected but if you click on another cell and try and paste the value by using [Ctrl] + [V] you will notice that the cell does not move and as such the short cut [Ctrl] + [X] is disabled.
I should note that there are some limitations to this technique. If you actually activate the cell and select the text in the cell itself and then try [Ctrl] + [X] you will find that the text will be cut, but if you use [Ctrl]+[X] on a normally selected cell you will notice [Ctrl] + [X] is disabled.
Saturday, January 19, 2008
How to create a formula to calculate mortgage in microsoft excel 2003?
One of the really cool parts aspects of Microsoft Excel is the functions Microsoft has created for you to use. This means that rather than have to develop a function from scratch you can use pre-built ones to do a plethora of tasks like Building your own Mortgage Calculator. The Mortgage Calculator or PMT function is just one of many Financial Functions available.
Okay, so how to build a mortgage calculator…
The first thing we have to do is to start by setting up a few basic headings. So lets begin by starting a new workbook and clicking in the first cell A1. Enter into cell address A1 the heading - Monthly Loan Repayments. Next off, enter into cell address A2 - Amount of Loan, cell address A3 - Interest Rate, cell address A4 - Length of Loan and then in A6 - Monthly Repayment.
In example mortgage calculator, we will take the Loan Amount, Interest Rate and Length of Loan and calculate your Monthly Repayment. Okay so in the corresponding field B1 enter the value of $200,000 and make sure you format the field as a currency. In cell B2 enter a value of 9.25% and format the field as a percentage and then finally enter in a value for the Length of the Loan as 25. The value you enter into the Length of the Loan field is in years.
Now its time to create the formula that will do your calculation for the Monthly Repayment. The function we will use for this calculation is called the PMT function. The PMT function always returns a negative number so one of the things we will need to do is to convert it into a positive number, but a little on that later.
There are three arguments we will use for this formula and they are -
= PMT(Monthly Interest Rate, Number of Payments, Amount Borrowed)
So to work out the Monthly Interest Rate we simply take the value in B3 and divide it by 12 - B3/12. The PMT function works on the basic of the number of payments you are going to make, so if we are going to make monthly payments on our mortgage we simply take the number of years in cell B4 and multiply it by 12 - B4 *12.
This means that to calculate the Monthly Repayment for our mortgage we need to enter the following formula -
= PMT(B3/12, B4*12, B2)
Now as I said before, the PMT function always returns a negative value, so to turn this into a positive value we simply type the PMT function with the Absolute Function encapsulating it as shown below -
= ABS(PMT(B3/12,B4*12,B2))
Simply type the formula above into the cell B6 and press the enter key. You must now format the cell address B6 as a currency and you can do that by simply pressing the Dollar Symbol on the Formatting Toolbar. As soon as you enter the formula and press enter you should get a result of $1712.76. If you do not get this answer, simply go back and make sure that you have entered the formula correctly.
The cool part about this Mortgage Calculator is that you can go back and change any one of the values in B2, B3 and B4 which are the Loan Amount, Interest Rate and Length of Loan to work out what your monthly mortgage repayments will be.
The cool part about this simple tool is that it tells you really quickly whether borrowing massive amounts from the bank is worth it and whether you can really afford that mortgage. Why not check out what your repayments will be if your interest rate went up by 2 or 3%, it can be really interesting to see the impact on your budget.
Simple tools like this can save you thousands of dollars and can also help you see what changes interest rates will have on your own budget. It is certainly worthwhile building yourself a Budgeting Spreadsheet and the mortgage calculator to work out just what you really can afford especially in these uncertain times.
Okay, so how to build a mortgage calculator…
The first thing we have to do is to start by setting up a few basic headings. So lets begin by starting a new workbook and clicking in the first cell A1. Enter into cell address A1 the heading - Monthly Loan Repayments. Next off, enter into cell address A2 - Amount of Loan, cell address A3 - Interest Rate, cell address A4 - Length of Loan and then in A6 - Monthly Repayment.
In example mortgage calculator, we will take the Loan Amount, Interest Rate and Length of Loan and calculate your Monthly Repayment. Okay so in the corresponding field B1 enter the value of $200,000 and make sure you format the field as a currency. In cell B2 enter a value of 9.25% and format the field as a percentage and then finally enter in a value for the Length of the Loan as 25. The value you enter into the Length of the Loan field is in years.
Now its time to create the formula that will do your calculation for the Monthly Repayment. The function we will use for this calculation is called the PMT function. The PMT function always returns a negative number so one of the things we will need to do is to convert it into a positive number, but a little on that later.
There are three arguments we will use for this formula and they are -
= PMT(Monthly Interest Rate, Number of Payments, Amount Borrowed)
So to work out the Monthly Interest Rate we simply take the value in B3 and divide it by 12 - B3/12. The PMT function works on the basic of the number of payments you are going to make, so if we are going to make monthly payments on our mortgage we simply take the number of years in cell B4 and multiply it by 12 - B4 *12.
This means that to calculate the Monthly Repayment for our mortgage we need to enter the following formula -
= PMT(B3/12, B4*12, B2)
Now as I said before, the PMT function always returns a negative value, so to turn this into a positive value we simply type the PMT function with the Absolute Function encapsulating it as shown below -
= ABS(PMT(B3/12,B4*12,B2))
Simply type the formula above into the cell B6 and press the enter key. You must now format the cell address B6 as a currency and you can do that by simply pressing the Dollar Symbol on the Formatting Toolbar. As soon as you enter the formula and press enter you should get a result of $1712.76. If you do not get this answer, simply go back and make sure that you have entered the formula correctly.
The cool part about this Mortgage Calculator is that you can go back and change any one of the values in B2, B3 and B4 which are the Loan Amount, Interest Rate and Length of Loan to work out what your monthly mortgage repayments will be.
The cool part about this simple tool is that it tells you really quickly whether borrowing massive amounts from the bank is worth it and whether you can really afford that mortgage. Why not check out what your repayments will be if your interest rate went up by 2 or 3%, it can be really interesting to see the impact on your budget.
Simple tools like this can save you thousands of dollars and can also help you see what changes interest rates will have on your own budget. It is certainly worthwhile building yourself a Budgeting Spreadsheet and the mortgage calculator to work out just what you really can afford especially in these uncertain times.
Thursday, January 17, 2008
How to sum cells by Using labels in Microsoft Excel 2003?
Microsoft Excel is a fantastic tool but one of its downfalls is the use of cell addresses especially when you are trying to sum a series of numbers; however Microsoft has a really cool tool that allows you to add up a series of cells simply using the labels around your data. In this article we will investigate the steps you need to follow to be able to use Labels to Sum cells in Microsoft Excel.
Let us get started …
To show you how using labels works, the first step we need to undertake is to simply create a new set of data, so open a new Microsoft Excel workbook and click on Sheet 1. First off we are going to build the worksheet so in cell A1 I want you to type - Years, in cell B1 type the word Values and in cell C1 type the word Values1 and in cell D1 type the word Total. These four values we have typed into cell A1, B1, C1, D1 are labels. We are now going to put into the worksheet three more labels. In cell A2 type the value 1999, in cell A3 type the value 2000 and in cell A4 type the value 2001. The last three values entered will in fact become labels but we will convert them to labels a little latter on.
In the remaining cells simply put the following values:
B2 29 C2 32
B3 54 C3 99
B4 62 C4 72
Now that we have built a very simple spreadsheet and we have a few values to work with we have to tell Microsoft Excel to actually accept labels in our formulas in our spreadsheet. We do this by first going to the Tools menu and then choosing the Options command from the drop down menu. The Options dialog box will now be open in front of you. Simply choose the Calculation tab and in the bottom right hand corner you will see a check box that says Accept Labels in Formulas and you need to click on the check box so that it has a tick in it. Then to complete the process simply press the OK button.
Now we can use the labels we put in B1 and C1 to add up the values.
Lets try it out…
In cell B5, which is the Values column I want you to type the formula -
= Sum(Values)
The cell should return the total of 145. What you will notice though is that the formula looked up the column to where the label was and said everything in this column will be added together. We could have simply typed the following formula instead of using labels -
= Sum(B2:B3)
However, as I am sure you will agree, using labels makes your formulas a lot easier to read and much clearer to understand. Now it is your turn. In the cell C5 write the equivalent label formula for that cell. I will give you a hint if you are not sure -
= Sum(Values1)
How did that go?
Alright, the next issue we are going to visit is using numbers as formula labels. Now if we simply typed in cell D2 the formula -
= Sum(1999)
All that would be returned is the value 1999 so this will not work at all. In fact what we need to do is to tell Microsoft Excel that we want the cells in A2, A3 and A4 to be treated as labels. The first step we must do is to select cells A2,A3 and A4, then go to the Insert menu, choose Name from the drop down menu and then choose the Label command from the expanded menu. The Label Ranges dialog box will now be visible in front of you. All you simply have to do, to have the cells we chose before defined as Labels is to press the Add button and then press the OK button.
Click once on the cell D2. What we are going to do now is to type in the same formula I mentioned before and that is -
= Sum(1999)
By the way, press the Enter key if you have not done so already. As soon as you do that you should see that it adds up the values in cells B2 and C2 and you will see the value of 61 in your cell.
Try creating the formulas for the other two cells -
D3 = Sum(2000)
D4 = Sum(2001)
Finally, just to finish our spreadsheet off , we can total all of our total values in cells D2, D3 and D4 by typing the following formula in D4 -
= Sum(Total)
Using labels in Microsoft Excel is a very clean way of summing your values as it ensures that you do not miss a cell address and it makes the formulas a lot simpler. One issue that I do teach my students is not to put blank rows in your spreadsheets as I have seen cases where the blank rows actually affected the ability of the application to work out what was a label and what is not. By following the design process I outlined above you will find that you have absolutely no troubles in using Labels to sum cells in your spreadsheets.
Let us get started …
To show you how using labels works, the first step we need to undertake is to simply create a new set of data, so open a new Microsoft Excel workbook and click on Sheet 1. First off we are going to build the worksheet so in cell A1 I want you to type - Years, in cell B1 type the word Values and in cell C1 type the word Values1 and in cell D1 type the word Total. These four values we have typed into cell A1, B1, C1, D1 are labels. We are now going to put into the worksheet three more labels. In cell A2 type the value 1999, in cell A3 type the value 2000 and in cell A4 type the value 2001. The last three values entered will in fact become labels but we will convert them to labels a little latter on.
In the remaining cells simply put the following values:
B2 29 C2 32
B3 54 C3 99
B4 62 C4 72
Now that we have built a very simple spreadsheet and we have a few values to work with we have to tell Microsoft Excel to actually accept labels in our formulas in our spreadsheet. We do this by first going to the Tools menu and then choosing the Options command from the drop down menu. The Options dialog box will now be open in front of you. Simply choose the Calculation tab and in the bottom right hand corner you will see a check box that says Accept Labels in Formulas and you need to click on the check box so that it has a tick in it. Then to complete the process simply press the OK button.
Now we can use the labels we put in B1 and C1 to add up the values.
Lets try it out…
In cell B5, which is the Values column I want you to type the formula -
= Sum(Values)
The cell should return the total of 145. What you will notice though is that the formula looked up the column to where the label was and said everything in this column will be added together. We could have simply typed the following formula instead of using labels -
= Sum(B2:B3)
However, as I am sure you will agree, using labels makes your formulas a lot easier to read and much clearer to understand. Now it is your turn. In the cell C5 write the equivalent label formula for that cell. I will give you a hint if you are not sure -
= Sum(Values1)
How did that go?
Alright, the next issue we are going to visit is using numbers as formula labels. Now if we simply typed in cell D2 the formula -
= Sum(1999)
All that would be returned is the value 1999 so this will not work at all. In fact what we need to do is to tell Microsoft Excel that we want the cells in A2, A3 and A4 to be treated as labels. The first step we must do is to select cells A2,A3 and A4, then go to the Insert menu, choose Name from the drop down menu and then choose the Label command from the expanded menu. The Label Ranges dialog box will now be visible in front of you. All you simply have to do, to have the cells we chose before defined as Labels is to press the Add button and then press the OK button.
Click once on the cell D2. What we are going to do now is to type in the same formula I mentioned before and that is -
= Sum(1999)
By the way, press the Enter key if you have not done so already. As soon as you do that you should see that it adds up the values in cells B2 and C2 and you will see the value of 61 in your cell.
Try creating the formulas for the other two cells -
D3 = Sum(2000)
D4 = Sum(2001)
Finally, just to finish our spreadsheet off , we can total all of our total values in cells D2, D3 and D4 by typing the following formula in D4 -
= Sum(Total)
Using labels in Microsoft Excel is a very clean way of summing your values as it ensures that you do not miss a cell address and it makes the formulas a lot simpler. One issue that I do teach my students is not to put blank rows in your spreadsheets as I have seen cases where the blank rows actually affected the ability of the application to work out what was a label and what is not. By following the design process I outlined above you will find that you have absolutely no troubles in using Labels to sum cells in your spreadsheets.
Tuesday, January 15, 2008
how to use formatting toolbar to format cells in microsoft excel 2003?
Microsoft Excel 2003 provides several formatting tools to help you format information stored in the cells of a worksheet. There are several convenient ways to format cell information, but this article will cover the Formatting Toolbar.
Excel provides a formatting toolbar, which can be made to appear in the document window. The formatting toolbar provides quick access to the most common formatting commands.
To enable the Formatting Toolbar, choose View > Toolbars and select the Formatting Toolbar. The Formatting Toolbar allows you to adjust the font and font size, text attributes, and text alignment. The Formatting Toolbar also contains a one-click button that will allow you to merge several selected cells together and center text inside them.
The Formatting Toolbar also contains a Currency Style button that allows you to quickly format currency figures. To configure the Currency Style properties, right click on the currency style button and set the properties according to your preference. Each time you use the Currency Style button, these properties will be applied. Other quick formatting buttons include the Percent Style and Comma Style buttons, which control how percentages and commas are used on cell data.
The Formatting Toolbar also contains buttons that allow you to increase or decrease the precision of a real number, by shifting the decimal point to the left or right. You can also increase or decrease text indentations, control the application of grid lines, and change the color of the cell background and the text color within a cell.
Excel 2007 has created a "ghosted" quick access palette that has now made this unnecessary so this will not be relevant to Office 2007 users, well done Microsoft!
Excel provides a formatting toolbar, which can be made to appear in the document window. The formatting toolbar provides quick access to the most common formatting commands.
To enable the Formatting Toolbar, choose View > Toolbars and select the Formatting Toolbar. The Formatting Toolbar allows you to adjust the font and font size, text attributes, and text alignment. The Formatting Toolbar also contains a one-click button that will allow you to merge several selected cells together and center text inside them.
The Formatting Toolbar also contains a Currency Style button that allows you to quickly format currency figures. To configure the Currency Style properties, right click on the currency style button and set the properties according to your preference. Each time you use the Currency Style button, these properties will be applied. Other quick formatting buttons include the Percent Style and Comma Style buttons, which control how percentages and commas are used on cell data.
The Formatting Toolbar also contains buttons that allow you to increase or decrease the precision of a real number, by shifting the decimal point to the left or right. You can also increase or decrease text indentations, control the application of grid lines, and change the color of the cell background and the text color within a cell.
Excel 2007 has created a "ghosted" quick access palette that has now made this unnecessary so this will not be relevant to Office 2007 users, well done Microsoft!
Monday, January 14, 2008
Which are the 5 most used features in microsoft excel 2003?
We all have been impressed at some point by some magnificent graph created and presented by a colleague during a meeting. Those great charts were probably created in Microsoft Excel; in fact, this program is a must in any office environment. We all use Excel to do all kinds of things like handling sales figures, number calculation, data tracking and even to create those cool looking graphs. To give you a high overview of the capabilities of Microsoft Excel, here are its top five used features:
1. Data logging and tracking – you can’t do this in Word because when you have a lot of information to track you’ll find that using Excel can really save you time - and your sanity. You can enter number figures, names, addresses, months, dates, and any information and track it in a workbook.
2. Number calculation – You can add, multiply, and sales information. You can combine totals from different locations and create new totals. You can select from a large range of mathematical calculations options.
3. Create lists and group information – you can easily and quickly list series of number or words. You can group lists of information by filtering out pieces that you don’t need.
4. Accounts Payable – Many small businesses use Microsoft Excel to track their expenses and gains instead of using fancy accounts payable programs. Even internal departments within major corporations use excel spreadsheets and workbooks to track their expenses.
5. Graphs and charts – you can create fantastic looking graphs using all this information that you track, log, group and list. You can see the relationships between different sets of data by portraying the data in a graph or chart.
1. Data logging and tracking – you can’t do this in Word because when you have a lot of information to track you’ll find that using Excel can really save you time - and your sanity. You can enter number figures, names, addresses, months, dates, and any information and track it in a workbook.
2. Number calculation – You can add, multiply, and sales information. You can combine totals from different locations and create new totals. You can select from a large range of mathematical calculations options.
3. Create lists and group information – you can easily and quickly list series of number or words. You can group lists of information by filtering out pieces that you don’t need.
4. Accounts Payable – Many small businesses use Microsoft Excel to track their expenses and gains instead of using fancy accounts payable programs. Even internal departments within major corporations use excel spreadsheets and workbooks to track their expenses.
5. Graphs and charts – you can create fantastic looking graphs using all this information that you track, log, group and list. You can see the relationships between different sets of data by portraying the data in a graph or chart.
Friday, January 11, 2008
How to copy email addresses from Microsoft Outlook to microsoft excel 2003?
To copy the email addresses from your Microsoft Outlook distribution list and paste them into Microsoft Excel, complete the following steps:
Open Outlook and select contacts. You will need to locate your distribution list within your contacts. If you don't see them, change you view by selecting View/Current View/Address Cards. This view allows you a good view of the card details so distribution list are easily identifiable. Once located, open the distribution list.
Select File and then select Copy to Folder. (If you are using Outlook 2007, you will need to add this copy to folder button onto your ribbon). Once you have selected the Copy to Folder button, click on OK.
The contents of your distribution list is now contained in an email style document. To select the names and email addresses, use your mouse and drag over the name and addresses you wish to copy.After selecting them, select Edit/Copy.
After copied, you can then paste the contents into Microsoft Excel by selecting Edit/Paste. You may need to clean up the format but this beats typing the entire distribution list all over again.
This article is helpful if you plan to do a mass email mailing from Microsoft Word. Saving the information from Outlook into Excel allows you have a source for the email addresses when prompted by Word. This is helpful because you can select some or all of the information from the distribution list.
You may need to use the TRIM function to cleanup the email addresses.
Open Outlook and select contacts. You will need to locate your distribution list within your contacts. If you don't see them, change you view by selecting View/Current View/Address Cards. This view allows you a good view of the card details so distribution list are easily identifiable. Once located, open the distribution list.
Select File and then select Copy to Folder. (If you are using Outlook 2007, you will need to add this copy to folder button onto your ribbon). Once you have selected the Copy to Folder button, click on OK.
The contents of your distribution list is now contained in an email style document. To select the names and email addresses, use your mouse and drag over the name and addresses you wish to copy.After selecting them, select Edit/Copy.
After copied, you can then paste the contents into Microsoft Excel by selecting Edit/Paste. You may need to clean up the format but this beats typing the entire distribution list all over again.
This article is helpful if you plan to do a mass email mailing from Microsoft Word. Saving the information from Outlook into Excel allows you have a source for the email addresses when prompted by Word. This is helpful because you can select some or all of the information from the distribution list.
You may need to use the TRIM function to cleanup the email addresses.
Tuesday, January 8, 2008
Using the CONFIDENCE function in microsoft excel 2003
Confidence intervals often give you useful insights into data sets you're trying to better understand. A confidence interval is the interval around a sample mean into which you expect the population mean to fall a certain percentage of the time.
If you have a sample of size n and know the sample mean m and population standard deviation sigma (s), you can find the range into which the actual population mean will fall x% of the time. Common confidence levels are 90%, 95%, and 99%.
Using the Confidence Function
The CONFIDENCE function uses the following syntax:
=CONFIDENCE (alpha,s,n)
Alpha is the significance level. It equals 1 minus the confidence level (expressed as a decimal). The s argument is the standard deviation of the data set. The n argument gives the number of items in the sample.
An Example Confidence Interval Calculation
For example, if a sample of 500 college graduates shows that they owe an average of $12,000 in student loans at graduation and the population standard deviation is $2,000, you can find a 95% confidence interval estimate of the population mean amount owed.
To do this using the CONFIDENCE function, enter alpha .05 as the first argument, the standard deviation 2000 as the second argument, and n 500 as the third argument. The function looks like this:
=CONFIDENCE (0.05,2000,500)
The function returns the value 175.30. So you can say with 95% confidence that the population mean is $12,000 plus or minus $175.30. (Note that if you have Microsoft Excel installed on your computer, you can copy the text shown above and paste the text into an Excel cell to make the calculation on your computer.)
One final note should be made here: If the value of the population standard deviation is unknown, you can use the value of the sample standard deviation as the point estimate of the population standard deviation.
If you have a sample of size n and know the sample mean m and population standard deviation sigma (s), you can find the range into which the actual population mean will fall x% of the time. Common confidence levels are 90%, 95%, and 99%.
Using the Confidence Function
The CONFIDENCE function uses the following syntax:
=CONFIDENCE (alpha,s,n)
Alpha is the significance level. It equals 1 minus the confidence level (expressed as a decimal). The s argument is the standard deviation of the data set. The n argument gives the number of items in the sample.
An Example Confidence Interval Calculation
For example, if a sample of 500 college graduates shows that they owe an average of $12,000 in student loans at graduation and the population standard deviation is $2,000, you can find a 95% confidence interval estimate of the population mean amount owed.
To do this using the CONFIDENCE function, enter alpha .05 as the first argument, the standard deviation 2000 as the second argument, and n 500 as the third argument. The function looks like this:
=CONFIDENCE (0.05,2000,500)
The function returns the value 175.30. So you can say with 95% confidence that the population mean is $12,000 plus or minus $175.30. (Note that if you have Microsoft Excel installed on your computer, you can copy the text shown above and paste the text into an Excel cell to make the calculation on your computer.)
One final note should be made here: If the value of the population standard deviation is unknown, you can use the value of the sample standard deviation as the point estimate of the population standard deviation.
Saturday, January 5, 2008
How to import data in excel?
Importing data to MS Excel is the process of transferring data from other database sources and converting the data into .xls format. This process eliminates the need to manually enter every item into a new Excel worksheet. Importing data therefore saves the user a lot of time and reduces human error the best way possible.
The source data is called internal data source if it is located in files or folders in your own personal computer.
Importing Data through the Import Data Functionality
On a new Microsoft Excel worksheet, click on the Taskbar menu labeled Data then scroll down to Import External Data. A new menu will pop up on the right. Select Import Data to open the Select Data Source dialog box. Browse over the files in your computer to find your data source or file.
Just remember that the data should be in a format supported by Excel. You will know if your data is importable to Excel if it appears in the default Select Data Source dialog box settings Files of type, All Data Sources.
Once you have selected your data source or file, click Open. The Select Table dialog box will appear. Highlight the particular worksheet you wish to import and click the button OK at the bottom of the dialog box.
The Import Data window will pop up in consequence where you will be asked where you want to put your imported data. Click the Existing Worksheet button if you wish to put the data in your current worksheet or click the button for New Worksheet if you want to add another worksheet to your current MS Excel file to accomodate imported data.
After you have made your choice, click OK at the upper right corner of the window to initiate the data import. If you wish to import other worksheets from the same file, repeat the process. Just select another worksheet when you get to the Select Table window.
Importing Data through Copying and Pasting
If you are going to import data from another .xls file, importing data to a new Excel file can be done through copying and pasting. Simply open a new workbook file or a new worksheet in an existing Excel file. Open, too, the source Excel workbook.
In your source Excel file, select the worksheet you wish to import by clicking on the cell at the upper left corner, the apex cell between Column A and Row 1. This will highlight all the values in the source worksheet.
Right click then select Copy from the pop-up menu or select Edit then Copy from the Excel worksheet Menu Taskbar. Click on your destination Excel workbook tab then click the same apex cell between Column A and Row 1 on your destination worksheet. Right click then select Paste from the right click menu or select Edit from the Menu Taskbar and scroll down to Paste. This will transfer all values, formats, and formula from the source worksheet to the new Excel file.
Subscribe to:
Posts (Atom)
