I’m entering a person’s expenses into a spreadsheet.
In cell, say, B10, I enter a $20 expense. Later, I learn that I want to add another $35 to that same cell. I press F2, hit the “home” key, add an = sign, press the “end” key, add a + sign, then enter 35.
Surely there’s a faster way to do this.
By the way…I think I’m using Excel 2013.
sumif may be helpful, but if you’re doing data entry as it is, it would only be marginally helpful.
Hit “=” before you type 20 the first time?
^I thought about that, but that’s a lot of =ing, especially when you don’t know if you’ll need to use that cell again.
Would it be helpful to add = to an entire column at once? You could do that pretty easily in a couple of steps. I can’t think of many other options, unless you want to reformat the spreadsheet inserting additional columns and use a sum formula as already suggested.
^How do you add an = to a whole column? That would be very helpful indeed.
Sumif…not so much.
Do you have to hardcode the sum in one cell? Why not put both numbers in separate cells and then reference them?
Say your data is in column B starting with B1, in C1 (insert new column if needed) enter ="="&B1 and drag formulas down. Highlight column C, copy and paste special values. Keep column C highlighted and press ctrl+H to find and replace = with =. This last step should reformat the numbers from looking like =20 to just 20. If you already have = signs in column B you’d obviously need to copy and paste col B as values so as not to add any double =.
^You’re killing me Smalls!
I could do this, but when a guy gives money to church every week, then you’d have 52 different cells for charitable contributions.
And if he has 30 different oil and gas wells, and each well has seven different expense categories, and there are 12 months in a year, then you have 30x7x12 = a whole lot of cells, and my mind is only so big.
And you are planning to hardcode those 52 different numbers in one cell? That’s a recipe for disaster! Create a charitable contribution tab and reference the total in your main working tab.
2520 cells is not a big data set. And you can always aggregate/filter it using pivot table.
It’s not if you’re downloading it.
It is if you’re entering it.
The point of a spreadsheet is to have multiple numbers in multiple categories and organize them neatly.
I’m with the krnyc on this one.
But just so you know, you don’t have to hit =, you can hit the +, so you don’t have to leave the 10 key
But you have to enter it no matter how you chose to organize it, no?
Greenie, why are you storing data in Excel? And worse, multiple values in the same cell. Do things right and store your data in Access and pull it into Excel, eff dude.
Takes thirty seconds to open a spreadsheet template. Takes 30 minutes to set up an Access file. We have appoximately zero customers who want to pay for that.
And if I really wanted to set something like that up, I’d use Quickbooks, not Access.
Right, have a separate column for charitable contributions, then put the sum (or sumIF if it needs more logic) in your cell B10.
If you just need to change B10 once in a while, then the =20+35 is fine, but if you know that there are new amounts constantly coming in, it’s best to set up your sheet to handle it. My guess is that if you are bothering to ask for solutions here, it’s annoying enough that it makes sense track all the sums separately.
In my business expense tracker, which is basically an expense ledger, I have the following columns:
Date | Category | Price | Description
(I sometimes add a % business use and % buisness price columns but have now tended to combine these in the price column by doing something like " =150.29*50% ", because most of my business uses are 100% anyway)
Then for each unique category I have a SUMIF() that adds up all the prices in the same category and puts it in a little table of Category | Totals
Now, whenever there’s a new expense, I just give it a date a price and a category and I let everything else I’m doing reference the Category | Totals table.
It’s a little tricky the first time you set up the template, but it’s really useful to keep in a general ledger tab. You can do Income as well, though I tend to track that in a separate sheet. I’ve tended to use OFFSETs make the SUMIFs adjust dynamically to the number of entries, though I might use Excel Tables if I were to redesign it from scratch today.
A faster way to do what you are trying to do is to go to the B10 cell, press the 5 key and then press the 5 key again.
^At first, I had to stop and think “What does the 5 key do, and why would you press it twice?”
Then I figured it out.