Ask Dave Taylor
  • Facebook
  • Instagram
  • Linkedin
  • Pinterest
  • Twitter
  • YouTube
  • Home
  • YouTube Videos
  • Top Categories
  • Subscribe via Email
  • Ask A Question
  • Meet Dave
  • Home
  • Outlook & Office 365 Help
  • How To Get Started Building an Excel Spreadsheet?

How To Get Started Building an Excel Spreadsheet?

February 15, 2021 / Dave Taylor / Outlook & Office 365 Help / 1 Comment

I’d like to learn how to use Microsoft Excel to do basic financial calculations like balance my checkbook. Yes, I write checks! Can you show me some of the basics in Excel, please?

I don’t know if I’m supposed to keep this a secret so I don’t sound too nerdy, but I actually love working on spreadsheets. Both the formatting and the formula entry are fun and satisfying in much the same way that adding a piece to a jigsaw puzzle is fun. Watch an old movie and you’ll see people with their big ledgers, painstakingly entering lines of data and calculating the addition or subtraction of values to keep a running total. It’s painful. Ever since the introduction of VisiCalc back in 1979, digital spreadsheets have been one of the killer apps for computers overall. Now, of course, they’re quite a bit more sophisticated, but a checkbook ledger is quite straightforward: You subtract checks you write, and you add deposits you make.

The main thing to realize when you’re building a spreadsheet is that each box on the spreadsheet is a “cell” and that a cell can contain text, numbers, dates, unformatted text or a formula. The formula itself can then depend on and reference other cells in the spreadsheet, which is where it gets so interesting. This way you can have a fixed value for, say, starting balance, then have all credits and debits (a credit is an addition to the account, and a debit is a subtraction) relative to that value. Change your starting balance and that change bubbles through the entire ledger, for a few, a few dozen or even hundreds of entries. It can be darn cool!

BUILD A BASIC EXCEL SPREADSHEET

To start out, open up Microsoft Excel and enter some sample transactions for a checking ledger. Those are usually comprised of five fields: check number, date, amount, payee and a running balance. Without any formatting at all, here’s my basic checkbook ledger:

microsoft excel basics - checkbook ledger - just text

Notice that line 6 is a deposit of $500. And just in time; the balance is running rather low, as you’ll see once we flesh out this spreadsheet! Let’s start with changing some cell background colors to make it a bit easier to read. And so, our first trick: Any formatting you can apply to one cell you can do to a group of cells by selecting them all first. So click and drag to select all five column titles, from Number (cell A1) to Balance (cell E1).

Then choose Cells > Format > Format Cells… from the “ribbon” (the toolbar at the top of Excel. It’ll look like this:

microsoft excel basics - checkbook ledger - format cell

Once you’re in the format cells area, there’s quite a lot you can do. First off, you can specify the data format so that currency values are displayed properly, dates, and, well, check it out:

microsoft excel basics - checkbook ledger - format cells - data type

Excel does a really good job of explaining things, so you can click on the various format options and it’ll show examples. But for right now, click on “Fill” along the top. A color palette shows up with lots of cheery fill (background) colors:

microsoft excel basics - checkbook ledger - fill color option cell formatting

You can go wild here, but I’ll be relatively restrained by using a subtle yellow background for the column heads. Choose the color, then click “OK” to proceed and the spreadsheet already looks better!

Now click on the “C”: Turns out you can click on a column head to choose every cell in that column, or a row number to choose every cell in that row. Here’s column “C” selected:

microsoft excel basics - checkbook ledger - fill color demo

Now instead of going through the clumsy menu system off the Ribbon instead right-click on one of the selected cells (on a Mac you’ll need to Control-click instead). A context menu pops up:

microsoft excel basics - checkbook ledger - context menu

Aha! “Format Cells…” is easier to access here. Notice that there’s also a keyboard shortcut too. On the Mac it’s Command-1. Easy.

Choose “Currency” from the formatting options and then do the same for the Balance column (column “E”). Now things are looking good and it’s time to…

ENTER A FORMULA INTO AN EXCEL SPREADSHEET

To calculate the running balance of your checkbook ledger, you need to use the formula previous balance – amount of check. To enter it as a formula, click on the cell where the value should be displayed, then type the equals sign “=”.

You’ve now moved into formula entry mode and for basic formulas it’s surprisingly easy. Just click on the cell you want to reference and its “address” will appear in the formula cell. Enter any mathematical notation to express the relationship between cells, then click on the next cell value you want to reference. Here’s what I get:

microsoft excel basics - checkbook ledger - enter formula

To produce this formula, I typed in “=”, clicked on the starting balance cell (E2), typed “-“, then clicked on the amount of this particular check, cell C3. Press Enter or Return and the value is calculated and you’ve got your first formula in your spreadsheet!

Here’s what most people don’t realize with Excel: You can copy and paste formula and it will automatically tweak it to make sense in the current cell. For example, now that the formula in cell E3 is correct, click on the cell and choose Edit > Copy, then click on the cell you want to duplicate the formula (immediately below):

microsoft excel basics - checkbook ledger - copy paste formula

Now just microsoft excel basics – checkbook ledger – and you’ll see it actually changes the formula to =E3-C4 as you would desire!

microsoft excel basics - checkbook ledger - formula pasted

Two things of note here: 1. You can always view a formula by clicking on the cell in question and 2. If you want to lock a specific cell reference location, add “$”. So if for some reason you wanted to have the second check be starting balance – check value regardless of where it is in the spreadsheet, use $E$2 instead of E2. That’s a general rule so you can use it even in quite complex formula!

Now you can just copy and paste the formula into each cell in the Balance column. Right? Wrong. What about that deposit?

One way you could address it is to always enter deposits as negative values, so a $500 deposit would be entered as -500, or, for that matter, deposits could be positive, but all check values are entered as negative numbers. Or you could just use a different formula for that one cell, where its value is the value of the cell above it PLUS the amount of the deposit:

microsoft excel basics - checkbook ledger - add value

Finally, here’s my check ledger spreadsheet:

microsoft excel basics - checkbook ledger - spreadsheet

To denote that deposit lines are different, I’m going to click and drag to select the cells in that row, then apply a light green fill to that set of cells. Easy to do, just like the yellow background fill for the column heads.

Where you can immediately see the value of this spreadsheet is when you realize that your starting balance was wrong. Instead of $435.30 it should have been $495.22. Since you have a spreadsheet based on actual formula calculations, changing that one cell at the top immediately changes every single value, offering a different final running balance as you would hope:

microsoft excel basics - checkbook ledger - new starting balance

Easy once you get the hang of it, right?

There’s a lot more you can do to make your spreadsheets useful, including a lot of formatting options (I’d probably change those date formats to start, and center align the column heads) but this should be enough to get you started creating your own Excel spreadsheet. Remember, big, complex spreadsheets start out just as rudimentary in the beginning too.

Pro Tip: I’ve been writing about computing basics and Microsoft Office since the early days. Heck, I remember VisiCalc on the Apple II. Please check out my extensive Microsoft Office 365 help area for lots and lots more useful tutorials while you’re visiting! Thanks.

Let’s Stay In Touch!

Never miss a single article, review or tutorial here on AskDaveTaylor, sign up for my fun weekly newsletter!
Name: 
Your email address:*
Please enter all required fields
Correct invalid entries
No spam, ever. Promise. Powered by FeedBlitz
Please choose a color:
Starbucks coffee cup I do have a lot to say, and questions of my own for that matter, but first I'd like to say thank you, Dave, for all your helpful information by buying you a cup of coffee!
checkbook balance, excel 365, intro to spreadsheets, microsoft excel, spreadsheet 101, spreadsheet basics

One comment on “How To Get Started Building an Excel Spreadsheet?”

  1. John says:
    February 17, 2021 at 12:37 pm

    Here is the most important two steps I’ve learned about creating a new Excel spread sheet:

    1. Type in a meaningful title in the top left cell. Hit enter.
    2. Save the spreadsheet so that it now has a real name and location.
    3. (Optional) Be sure the AutoRecover option is turned on.

    Then follow Dave’s guidance. Your work is much more likely to survive an oops.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Recent Posts

  • Possible to Pair Bluetooth Headphones with my Vizio TV?
  • Possible to Schedule a Windows 11 System Update & Restart?
  • How Can I Add Spacers and Divider Lines to my MacOS 12 Dock?
  • How Do I Pair Bluetooth Earbuds with my Google Chromebook?
  • How Can I Digitally Sign a PDF Document on my Windows PC?

On Our YouTube Channel

Google Pixel 6a Budget Android Smartphone -- DEMO & REVIEW

QueStyle M15 Mobile Lossless DAC / Headphone Amp -- UNBOXING & REVIEW

Categories

  • AdSense, AdWords, and PPC Help (106)
  • Amazon, eBay, and Online Shopping Help, (161)
  • Android Help (201)
  • Apple iPad Help (145)
  • Apple Watch Help (52)
  • Articles, Tutorials, and Reviews (344)
  • Auto Tech Help (11)
  • Business Advice (199)
  • Chrome OS Help (25)
  • Computer & Internet Basics (764)
  • d) None of the Above (165)
  • Facebook Help (383)
  • Google, Chrome & Gmail Help (179)
  • HTML & Web Page Design (245)
  • Instagram Help (47)
  • iPhone & iOS Help (607)
  • iPod & MP3 Player Help (173)
  • Kindle & Nook Help (93)
  • LinkedIn Help (85)
  • Linux Help (166)
  • Linux Shell Script Programming (87)
  • Mac & MacOS Help (894)
  • Most Popular (16)
  • Outlook & Office 365 Help (26)
  • PayPal Help (69)
  • Pinterest Help (53)
  • Reddit Help (18)
  • SEO & Marketing (81)
  • Spam, Scams & Security (92)
  • Trade Show News & Updates (23)
  • Twitter Help (217)
  • Video Game Tips (66)
  • Web Site Traffic Tips (62)
  • Windows PC Help (921)
  • Wordpress Help (204)
  • Writing and Publishing (72)
  • YouTube Help (46)
  • YouTube Video Reviews (159)
  • Zoom, Skype & Video Chat Help (57)

Archives

Social Connections:

Ask Dave Taylor


Follow Me on Pinterest
Follow me on Twitter
Follow me on LinkedIn
Follow me on Instagram


AskDaveTaylor on Facebook



microsoft insider mvp


This web site is for the purpose of disseminating information for educational purposes, free of charge, for the benefit of all visitors. We take great care to provide quality information. However, we do not guarantee, and accept no legal liability whatsoever arising from or connected to, the accuracy, reliability, currency or completeness of any material contained on this site or on any linked site. Further, please note that by submitting a question or comment you're agreeing to our terms of service, which are: you relinquish any subsequent rights of ownership to your material by submitting it on this site. Our lawyer says "Thanks for your cooperation."
© 2022 by Dave Taylor. "Ask Dave Taylor®" is a registered trademark of Intuitive Systems, LLC.
Privacy Policy - Terms and Conditions - Accessibility Policy