Basic Spreadsheet Explorations

Databases store information in an organized structure. Spreadsheets can also help us organize information much in the way a database does.

Keeping track of tasks in a spreadsheet will help us to keep a project organized. Using basic text formatting helps column headings to stand out.

Considering when, and when not, to create additional worksheets in the same workbook. Adding contact information on a new worksheet and understanding the relationship between the Contact Info and Task List worksheets.

Adding completion dates and estimated and final costs to the Task List worksheet. Examining formatting options for these and for alignment.

Exploring choices for creating custom formatting for cell data and tying these concepts to data types in databases.

Using the SUM function to generate basic formulas that let the spreadsheet calculate total costs instead of manually calculating these and putting them in the spreadsheet.

Noting that databases typically have a field in the table to uniquely identify each record, which we typically do not apply in simple worksheets.

Simple organizational tasks can be easily managed using a spreadsheet, but as size and complexity grow, managing the same tasks may be better done in a full database application. Knowing that the terminology maps from spreadsheet structure to database structure pretty cleanly will help to make the leap to database platforms when the time comes.

We take a look at the finished form first to see what we want to put together. Will we need an invoice, a bill, or a receipt?

Spreadsheet applications often provide useful templates that already have formatting set up for many common personal and business forms.

We can set the column width, row height, and merge cells to alter many aspects of visually placing our text exactly where we want it in forms.

We will look at ways to view how the form will appear on a printed page, and ways to force the form onto a single page.

Microsoft Excel has table objects, and this lesson is a quick, introductory look at these.

You own a store and at the end of a busy selling season, you want to find out how much revenue you made. Your staff has entered a list of sales records for a given batch that needs to be processed. Use spreadsheets to calculate Gross Sales for the batch of sales records.

You want to offer a discount to your customers. Does it make a difference whether the discount is applied to the order total or to the amount for each individual item before totaling? Use spreadsheets to apply discounts and to find out the answer to this question.

State and local governments actually determine when--and if--to apply sales tax. Does it make a difference, though, whether a discount is applied first and then sales tax, or whether you calculate the sales tax first and then apply a discount? Use spreadsheets to calculate sales tax and to answer questions like this.

The completed workbook, in Excel, is attached as a resource in this lesson.

We start out building our weekly timesheet by looking at some basic cell and number formats and exploring a very simple date calculation.

We take our first look at one of the spreadsheet's date functions, Weekday, which assigns an integer value to each date that represents a day of the week. We then play around with the date formats to get the cells to display the name of the weekday.

We start entering in some start and end times for our work shifts and explore some formatting options for time values and some very basic time calculations.

We continue building our timesheet by adding in columns and calculations for the final Net Time that was worked each day and then use this and our base pay rate to calculate a daily Gross Pay amount.

We enhance our timesheet by exploring different strategies for calculating a Running Total, which we use to add up each of the day's Gross Pay amounts into a total Weekly Pay for each week.

Finally, we use conditional formatting to highlight the Weekly Pay totals on the timesheet. The example Excel workbook that we completed is attached as a resource in this lecture.

We'll take a look back at all we covered in this course.

Come again and be on the lookout for my other explorations courses!