Forum Discussion
Excel - Entering consecutive months in different sheets
May I ask you a question? I'd like to know why you have a separate sheet for each month...often that's done because we approach the task as if Excel really was no more than a little more automated green ledger sheet--i.e., we use it to add and subtract to make keeping (for example) track of expenses on a monthly basis a bit easier.
In fact, if that's the kind of thinking behind your use, you could benefit from separating the input of expenses (by date, payee, budget category, amount) from a nice monthly report (the output).
That's just an example....and it's why I'm asking. There may be an entirely legitimate reason, but often it's a failure to understand how Excel can work to produce a nice report from a single input sheet covering all months (even multiple years).
Thank you for your input. First, I have to say that my excel knowledge is intermediate and I'm trying to become more proficient by practicing what I've learned in a class I attended. I do understand your point and have tried that approach (all the information in one sheet) before, but it is very confusing for me. I prefer to have the information separate for an easy access to each month and then prepare consolidated reports at the end of the year. The input is separated as you suggested on each sheet and while creating the worksheet I was able to use many of the tips and tricks I learned.
I am asking this question because I saw the instructor doing it, but failed to really understand how he did it :(. I'm now obsessed about it. 🙂
Again, thank you,
I'm not going to let you off that easily, so please forgive me for pushing back a bit here. The only reason you gave for continuing the course was that you find it confusing when everything is on one sheet. But I wasn't suggesting that everything be on one sheet; only the input. That is, only the "raw data."
Absolutely agree that it's helpful to have access to nicely arrayed monthly data--sometimes even having separate sheets for each month, sometimes having one sheet that dynamically displays the data for one month at a time (and sometimes that same sheet displaying the consolidated data for the year)...
So, may I ask, unless there's confidential information in your workbook, is it possible for you to upload it here to the techcommunity forum so that I or somebody else might be able to recommend an altogether different way to approach the task? If it does contain confidential info, perhaps a copy that's been stripped of confidential or personal info.
Thank you for not letting me out so easily 🙂 I know it is in my best interest, and I do want to learn. As I said, I'm trying to practice what I learned using a personal need such as my expenses/budget information. I'm attaching what I have worked so far.
If I understood correctly, you suggest me doing a main sheet with all the information, and then having excel create the monthly sheets for me? Still, I'd need to enter the monthly information on the main sheet. I'm sorry, I'm so dumb 😞
First of all, you're definitely not dumb. You are stuck in a particular way of looking at things, a particular paradigm. Probably based on a paper way of doing things. I'm not sure how you'd plan on entering the details, other than by going through your credit card and checking statements and entering details in each box.
I've just finished creating and launching my own tracking spreadsheet for tracking our household income and expenses. ALL OF THE DATA from 2019 and YTD 2020 is entered in one sheet. All of it. I have two checking accounts, one IRA (from which some of the income comes), four credit cards. I download from each of those accounts on a monthly basis, importing the data into that single sheet in Excel. I then categorize each item on the basis of the kind of categories and sub-categories you have in yours. Note: I have still stayed in the single "input" sheet, the one with all the "raw data." There are currently over 2,300 rows of raw data (for 16.5 months all of last year, plus 4.5 for this year).
A simple Pivot Table summarizes it all.
My own actual sheet is far too personal and too complicated to share--maybe I'll work on creating a template using it--but I'm attaching an example illustrating how the Pivot Table can summarize monthly data by category. That's ALL I mean for you to take from this. It's far too simple in the categories, but I'm sure you can see from it how the Pivot Table, by itself, can be used to create a meaningful summary, cross-tabulated.
Here's a link to a useful introductory YouTube video on Pivot Tables: https://www.youtube.com/watch?v=m0wI61ahfLc
Excel is wonderfully equipped to take that "messy" raw data sheet and extract a nicely formatted report--giving you the ability to control what is summarized in that report. Learn to use those abilities rather than doing all the heavy lifting yourself.