Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
In this worksheet YTD and Pct Increase, we want to calculate the Year-To-Date Profit. Now, we could do this with Sales or Overhead. We've just chosen to do this with Profits here. We'd like to have a running total month by month of how much profit we've made so far this year. In cell B5, we want to put in the January entry. Let's also be thinking ahead what we want to do in February and March. One thought, as we look ahead here might be, when it comes to February, we will want to add those two cells. When it comes to March, we will want to add these three, in April these four and so on.
That sounds like it might be somewhat complex, but let's approach it this way. What if we figure out how to put in our January Year-To-Date profit, then we can go to February and add these two cells. So a Year-To-Date calculation is relatively straightforward, but maybe a little tricky at first. First of all, let's keep in mind the following idea. Our January profit so far is 20. Maybe these are dollars or thousands of dollars, depends on the size of the operation, but our Year-To-Date profit should always be the same as our January profit.
We're not going to type in a 20 here because what if these numbers change? This number will change. Do we need to write a formula here that subtracts these two? Well, we could do that, but why don't we just say automatically, this cell here is always equal to the profit. In other words, equal B4, no matter what. If we change the Overhead to 95, Enter, these two numbers both change. This is always equal to the one above it. Let me undo that with Ctrl+Z. How about February? Earlier we had suggested we could add these two cells to get our cumulative profit--20 + 30 is 50.
We could also add these two-- 20 + 30 is 50--so what's best? Well, look ahead to March. In March, will we be adding just these two cells to get our Year-To-Date profit? It'll make more sense to take the March entry and add it on to the previous year-to-date, provided this Year-To-Date is the total of these two cells. So let's write a formula here, equal. Now, we can add these two cells in any order we want. It's either going to be C4 plus B5 or the reverse, B5 plus C4.
Remember you can click on cells if you wish-- do it this way--that's an answer. One way to express this formula is: it's the cell above, plus the cell to its left. Will that makes sense over here in March? It surely will. The cell above added to the cell to its left. The formula for February here is the one that we want to copy all the way across. We'll simply drag the corner here into June. A quick check is that our June Year-To -Date profit is the same as the total Profit that we have here.
Writing a simple formula like this ultimately is simple and straightforward, although initially, it might be a little tricky. I'm not saying in any particular way that this formula is needed by everybody, but it certainly is commonly used and it does bring out and emphasize the idea that cell references are really important when you build formulas in Excel.
There are currently no FAQs about Excel 2013 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.