Adding Columns and Updating Formulas in Excel: Step-by-Step Guide

Excel is a powerful tool for managing and analyzing data, but it can be daunting for beginners. This guide will walk you through the process of adding columns to your Excel spreadsheet and updating formulas to accommodate the new data. With clear examples and explanations, you’ll be able to master these essential skills and enhance your Excel proficiency.

 

How To Add New Columns & Update Formulas in Excel


Step 1: Open Your Excel Workbook

Begin by opening the Excel workbook where you want to add new columns. Make sure you have the necessary data ready for input.

 

Step 2: Select the Location for the New Column

Click on the lettered header of the column immediately to the right of where you want the new column to appear. For example, if you want to add a new column between columns B and C, click on the header labeled ‘C’.

 

Step 3: Insert the New Column

Right-click on the selected column header to open the context menu. Choose ‘Insert’ from the menu, and a new column will appear to the left of the selected column. Alternatively, you can use the Excel ribbon by going to the ‘Home’ tab, clicking on ‘Insert’, and then selecting ‘Insert Sheet Columns’.

 

Step 4: Add Data to the New Column

Enter the data into the new column as needed. If you’re adding calculated values, you may need to create or update formulas.

 

Step 5: Update Formulas to Include the New Column

If you have formulas in your spreadsheet that need to include the data from the new column, you’ll need to update them. Click on the cell with the formula you want to update, and you’ll see the formula in the formula bar at the top of the Excel window.

For example, let’s say you have a SUM formula in cell E1 that originally summed columns B and C:

`=SUM(B1:C1)`

After adding a new column between B and C, you need to update the formula to include the new column B (which pushed the original column C to D):

`=SUM(B1:D1)`

 

Step 6: Copy Updated Formulas

If you need to apply the updated formula to other cells in the same column, you can use the fill handle. Click on the cell with the updated formula, and then hover over the small square at the bottom-right corner of the cell until the cursor changes to a plus sign. Click and drag down to fill the formula into the cells below.

 

Step 7: Check for Errors

After updating formulas, it’s important to check for errors. Look for cells with error messages such as #REF! or #VALUE! and correct the formulas as necessary.

Example Table:

Before adding a new column:

| A | B | C | D |
|—|—|—|—|
| 1 | 2 | 3 | =SUM(B1:C1) |

After adding a new column between B and C:

| A | B | C | D | E |
|—|—|—|—|—|
| 1 | 4 | 2 | 3 | =SUM(B1:D1) |

In this example, the original sum in column D has been updated to include the new column C, and the formula in column E now sums the range from B1 to D1.

 

Conclusion:

Adding columns and updating formulas in Excel can seem complex, but by following these step-by-step instructions, you can easily manage your data and ensure your calculations are accurate. Remember to always check for errors after making changes to your spreadsheet. With practice, these tasks will become second nature, and you’ll be well on your way to mastering Excel.

 

Live Excel Support Transcript Example:

Below is a live transcript of one of our Excel experts helping a customer add columns and updating formulas:

Full Conversation:

Customer: Yes, I can barely hear you. Oh, is that better? That’s much better. All right. So, this is Carl from Geeker. What can I do for you?
Technician: I had a gentleman named Jason help me with a program, Excel spreadsheet program and everything works fine, but I found out I needed to add another column. You can remote log in to me so you can see it and you can see the column I added so you can help me modify the formula to work.
Customer: Okay, that sounds good. I will, oh, I don’t think I can enable remote until you join on the computer.
Technician: Until I what? I think you need to join on the computer for me to do the remote access.
Customer: I need to what on the computer?
Technician: I think you need to join the meeting on the computer.
Customer: Oh, okay. All right. Can you send me a join?
Technician: You just go to Geeker and you can join there. G-E-K-E-R.
Customer: All right, on demand. Log in. Oh, no. Wrong one. Sorry. I got to join. I’m already logged in. Join Geeker. Join Geeker. Nope. It said please verify your account. Click here. Resend verification email. No. All right. I’m on the Geeker website. Do I get help now?
Technician: Does it show that you’re already in it? Like is there a list of the meeting that you’re already in here with me?
Customer: No. That’s strange. Hold on. Live jobs. Ah. There you go. All right. In progress. This is October 12th. That’s okay. Right, and then there should be a button next to that that you can hit join. There’s a screen to the right of it that’s blocking. Scroll down. Maybe I’m not. Please call me. I want to call Geekr.
Technician: Okay, I’m not letting me see that we’re to the right of the screen. Oh, hold on, joint. There it is. All right. I had to minimize. Callsable. Yes. Okay. I don’t have a microphone. Recording is on. So now I can enable remote. Allow remote access and watch. Okay, I understand. Continue via phone chat. No. Let’s say let me. It’ll call me in a few minutes. You’re now. You’re not being billed yet. Yet. Yet. Yet. Um. I think I have a choice of. I’m sorry. I just put the link to the remote access thing in the chat.
Customer: All right. I see your name. You’re Casey in the circle. And yeah, if you go to the left of the meeting area, there’s a thing that says chat. And there should be a Zoho. There should be a zone.
Technician: Yep. I see the chat.
Customer: Okay. Do I hit the right?
Technician: No, that’s his type here. And then a connection speed is good above that. There should be a Zoho link that I pasted.
Customer: Share screen. We can do it that way if you don’t see the link.
Technician: Yeah. Do I share screen with Geeker search, Geeker 1 or Geeker 2?
Customer: I don’t know. I think I got it. I think I got it. Share.
Technician: All right. I can see that now. Okay. So what are you trying to do here?
Customer: Okay. So Jason, if you look at the formula bar top, did all that for me. What I had to do is I had to add a column E, which says minimum square feet. And then I also added it in the formula columns on column K.
Technician: Okay. So now what I need to do is if B9 is below the six minimum square feet, which is in the K column, I need it to say below min square feet. If I exceed, right now it says, let me go here, just hit 70. It says exceeds maximum glass size. But if it’s below six, if the total square feet is below six, I need it to say below min square feet. And he put the formula there and then had to create a column here to get it to show up in B9. So if you click on column O, depending on what glass size they choose, you can see the formula.
Customer: Oh, okay. Got it. So basically all I need to do is modify that formula to include a second error code stating below min square feet as referenced in column K.
Technician: Okay. Let me just look at the formula that he made. I think what you want to do, I mean, if you need to change it all, that’s fine.
Customer: No, no, no. We don’t need to do all that. I’m just trying to match up the parentheses.
Technician: Okay. So what you want to do, come up to the top of the thing, so the top O cell, O7, I think. Right. And then select the if statement that includes the exceed max size. So from that, from after where it says exceed max size back to the second if.
Customer: There’s the first if. All right. I’m on the second if.
Technician: Yep. Right. So click in that formula and select the text from that second if through to exceeds max size.
Customer: Through exceeds max size or up to?
Technician: Yeah, you can select that part also, and then we’ll just change it. But then after that parentheses, after exceed max size, you want to stop. And then we’re just going to copy that whole thing. Now, do I have to be inside the parentheses on the, on the if?
Customer: You want to select from if to the parentheses after exceeds max size, right? Just keep going. And then there. Nice. So hit control C to copy that. And then at the end, we’re going to replace those quotes with a new if. So delete those two. And then do if and then paste. Or maybe if parentheses paste. I don’t know if you copied the parentheses or not. I don’t remember if I did either.
Technician: Yeah. So just do a parentheses and then paste. And if we need to get rid of it, we will.
Customer: Okay. And then change exceed max size to whatever you want it to say when it’s too small.
Technician: Okay. After the quotation mark, but before the parentheses. So just one space, one character to the right. Do comma, double quote, double quote. Nice. And then we want to change where it says, oh, I lost your share there. You lost me? I did. I can’t see the share anymore.
Customer: Okay. And then, yes, I can see it now. Then change where it says J8, J17 greater than or equal to or less than or equal to. Change that to greater than or equal to. And then change the L’s to K’s. And that should do it. For which one? Hit okay. I want to see where it puts us. Oh, okay. Yeah. So delete that and delete the comma. Delete the comma? And the double quotes. Yeah. So then, okay. And then try hitting enter again. Okay. But then it’s giving us all the numbers, which isn’t necessarily what we want. Yeah, he had a different, down here he had the filter. Yeah. So. You want to drive? Yeah. That’ll probably be easier. Okay. What do I need to do? So over on the right side of the screen, do you see where there’s the chat box? Yeah. So there should be a, I’m going to put a new link there. Right there. If you click that, you can enable remote access. Connected. Let’s see where I need to go. I’ve got two icons on the right screen where it says KZ phoning. No, no, no. It’s not in the meeting itself. Over to the left of that. Okay. Remote. On the bottom of the screen, I see, I’m on the left of it, but I don’t see. There should be, so underneath where it says connection speed, there should be a box that says chat. Chat with technician. Yep. Right. And then in that, do you see a Zoho link? Yeah, I see two of them. Okay. Copy this. Use the second one there. Use the what? Use the second one. Yeah. All right. Nothing’s happening. It’s not a hyperlink. It’s not a hyperlink. You got to copy and paste it. Copy. And then just paste it into a new. Copy and paste. Open link. Yes. Zoho assist is initializing remote session. Yep. This takes it a minute. Okay. Nice. Okay. So, let’s do that. Okay. This is my second time with you guys. My first time with Jason was really, really good. I appreciated it. Well, hopefully, I’ll be able to get this working for you in just a moment. What did you want to say? Less than min square feet? That is correct. Okay. So, if we change this to, like, two, that should make it less than the minimum. It didn’t. There’s no error. Okay. Oh, oh, oh, oh. Let’s check those in the opposite order. Okay. Okay. There we go. The error code. That’s not. Ah. Does that have something to do? He put in the Q column? Q15. So, we can just make that Q15, and then that’ll work. All right. So, if we make this 70, that both updates. And if we make it 65, that’s in range. That’s too big. So, what’s something that would make it work? Like 20? I’m bad at math. Let’s choose. Oh, there you go. Yeah, so that works. 716th and yield type grade A is 14 square feet is the max. Six is the min. Right. Okay. It looks like we’re good. Nice. Yeah, so that should do it, I think. Okay, I appreciate it. Absolutely. Cool. I’m glad we were able to fix that. Is there anything else I can do for you right now? No, but I’m sure I’m going to have more, because I’ve got 17 more different spreadsheets to configure. Jeez, that’s a lot. I know. Well, I’m doing work for a window and door company. Yep. And one scenario just got me off the hook. I got everything else done on the other sheets, but I’ve got a bunch of different, because now this is like for a picture window, but I’ve got to get into the special shapes yet. And those have really weird configuration limitations. Got it. So, once I get that, I’ll be calling you back. And what’s your name again, so I can ask for you next time? Carl. Carl. Carl, thank you very much. You seem to know what’s going on here, so I’m going to request you. Well, I try. But, all right. We always try. Exactly. That’s all you can do, right? But, anyway, enjoy the rest of your day, and I will talk to you soon. All right, thanks. Bye.