Utilizing Excel Formulas

The feature I love most about Excel is its ability to handle formulas. This is an extremely powerful and flexible function as it allows you to analyze large amounts of data spread across multiple sheets and report on it easily. You can have cells which calculate sums, averages, standard deviation, and much more. Even better you can make formulas which depend on the results of other formulas. This “trickle down” functionality allows you to break a large problem into logical chucks so you can analyze your data even further.

In this section, I am going to walkthrough how to set up an Excel Workbook (a workbook is composed of several individual spreadsheets; known as Sheets) which utilizes the features above. We will set up a workbook to keep track of our finances. We will track a checking account, savings account and a credit card, each on a separate Excel Sheet and then tie everything together on a summary sheet. Finally, we will put together a chart which shows us the summary information graphically. Again, I will provide a screenshot for every step, so it is easy to follow along.

Initializing and formatting our data sheet:

Screenshot	Task Description
	On the first sheet, add your column header information. In this article we will keep track of the date of our transaction, a brief description, the amount and current balance for our account.
	Highlight columns A through D (a description of how to do this is explained in the walkthrough for sorting data), and then expand the width of column D so all our columns are not as narrow. Do this by clicking and dragging the vertical line separating column D from column E. All the selected columns will now be the same width.
	Highlight the entire first row (using the same method for highlighting an entire column), then select the menu option Format > Cells.
	In the Format Cells dialog box, select the Font tab, set the font style to bold, the underline style to single and then click Ok. This will make our column headers stand out from our data.
	Now we will format the first column, Date, to properly display date information. We can format an entire column by right clicking on the column letter heading (note, not the row of column labels, but the Excel column header which is the letter A in a grey box) and selecting Format Cells.
	In the Format Cells dialog box select the Number tab, set the category to Date and the type to be whatever date display you like best. Click Ok when you are done.
	Select the columns we will use for currency amounts (C and D) and select the Format Cells option using the same method we did above.
	In the Format Cells dialog box select the Number tab, set the category to Currency and select the currency format you prefer. Click Ok when you are done.
	Next, we will set the alignment for the Date and Description columns, so select both columns. Along the top toolbar select the option for Left Align. In my screenshot the window is compressed, so I accessed the additional toolbar items by clicking the extension arrow on the far right of the toolbar to have the options presented in a toolbox.
	To complete the formatting, select the Amount and Balance columns and right align them. This will ensure our digits and decimal points all line up correctly so the dollar amounts will be easier to read.

Pages: 1 2 3 4 5 6 7 8

Like what you read?

If so, please join over 28,000 people who receive our exclusive weekly newsletter and computer tips, and get FREE COPIES of 5 eBooks we created, as our gift to you for subscribing. Just enter your name and email below: