Using Quattro Pro and Excel

How do I calculate formulas in a spreadsheet?

Suppose you have raw data but you really want it in percentage form.   For example, as shown in the following table (taken from data off the U.S. Census web site, you have data on the number of blacks and the total population, but you really need the proportion black.

You can calculate the proportion by creating a formula.  In the proportion black cell for Anne Arundel, you would type the following: =B2/C2 and then hit the return key.   Notice that you can put the appropriate cells in the formula simply by using the arrow keys to move to cell.  After you hit the return key, "0.118259" (the proportion of blacks in Anne Arundel County) appears in the cell.

How can I sum a column of numbers?   calculate an average?

Suppose you want to know the total number of black people in all of these counties (see table below).  If you click on the cell at the bottom of the column, and then click on the capital sigma, the mathematical symbol for sum, in one of the toolbars on the topic of the screen.  The sum will appear (in Quattro Pro) or the function for summing a column will appear and you then just need to hit return (in Excel).  (I cannot seem to reproduce what a sigma looks like in HTML here but it looks somewhat like a capital E.)  If you did it right, the number 639560 should appear in the cell at the bottom of the column of numbers.

If you want to calculate the average, go into the formula bar (just above the spreadsheet itself), and type /9 to divide the sum by nine to get the average.  Obviously, if you have a different number of counties in your column, you will need to type that number in place of 9 to calculate the average correctly.

How do I copy the formula so I don't have to retype it over and over?

You copy the formula and then paste it into cells.  To do this quickly, click on the cell that already has the formula, then hit your right mouse button (a menu pops up), and click on "copy."  Then, highlight the cells in which you want to copy the formula (cells D2-D10 in my sample table).  Hit your right mouse button again, and click on paste.  The formula appears in all the cells.  The computer will adjust the formula so it calculates from the correct cells.  (Instead of using your right mouse button, you can also just click and drag on "Edit" in the toolbar in the top of the screen.)  You may want to check one of the new cells with the formula to make sure that the computer guessed directly how to adjust the original formula.

How can I reformat data in cells to make it easier to read?

Click on the cell or cells you want to reformat.  Click on the bold B in the toolbar to make the text bold.  Similarly, click on the italicized I to make the text italicized, or the underlined U to underline the text.

The long proportion "0.118259" is accurate but not easy to read.  In Excel, highlight the cell you want to change, hit your right mouse button and choose "Format Cells" to change the format of the number.  In Quattro Pro, choose "Cell Properties" and then click on "Numeric Format."  If you play around with this, I think you'll be able to figure out how to make the number shorter and read "0.12" or something equally pleasing to you.

How can I sort data?

Suppose you have the table below but you would like all of the data sorted so that the counties are all ordered from lowest proportion black to highest proportion black.  How do you do this?

Highlight all of the cells from A2 to D10.   In Quattro Pro, click on "Tools" in the toolbar on the top of the screen and choose "Sort..."  In Excell, click on "Data" in the toolbar on the top of the screen and choose "Sort..."  Select the column that you wish to sort by (put it in the "1st" box in Quattro Pro) and then hit enter.   Make sure the "ascending" box is checked if you want the data is sorted in ascending order.  If you have sorted the data properly, the table should look like this:

Go to my Home Page, the GOVT 423 Home Page, or the GOVT 423 Syllabus.

The views and opinions expressed in this page are strictly those of the page author. The contents of this page have not been reviewed or approved by American University. If you have any questions about this page or any of the links are outdated, please email David Lublin at dlublin@american.edu. This page was last updated on February 18, 1998.