Home

How to auto fill columns

%3CLINGO-SUB%20id%3D%22lingo-sub-765978%22%20slang%3D%22en-US%22%3EHow%20to%20auto%20fill%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765978%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20trying%20to%20simplify%20a%20list.%20I%20have%20two%20columns%20to%20work%20with%20in%20this%20scenario.%20I%20want%20Excel%20to%20autofill%20column%20B%20if%20column%20A%20says%20XXX.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%20if%20column%20B%20%3D%20London%2C%20I%20want%20column%20A%20%3D%20United%20Kingdom.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20originally%20though%20I%20could%20use%20the%20vlookup%20function%2C%20but%20I%20believe%20that%20only%20allows%20the%20destination%20to%20be%20one%20cell.%20I%20want%20Excel%20to%20auto%20fill%20the%20next%20column%20over%20if%20the%20column%20next%20to%20it%20says%20a%20certain%20word.%20This%20seems%20like%20an%20easy%2C%20task%2C%20but%20I%20can't%20find%20a%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-765978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766048%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20auto%20fill%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379557%22%20target%3D%22_blank%22%3E%40ddelise%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20not%20quite%20sure%20why%20you%20don't%20auto-fill%2C%20but%20the%20best%20idea%20would%20be%20to%20put%20your%20data%20in%20a%20Table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20if%20you%20have%20the%20word%20'Country'%20(or%20whatever%20-%20the%20UK%20isn't%20a%20country%20I%20know%20before%20anyone%20has%20a%20go%20at%20me)%20in%20cell%20A1%20and%20city%20in%20cell%20B1.%26nbsp%3B%20Then%2C%20put%20your%20VLOOKUP%20formula%20in%20cell%20A2%20and%20your%20city%20in%20cell%20A2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20%2C%20highlight%20cells%20A1%3AB2%20and%20Insert-%26gt%3BTable%20(CTRL%20%2B%20T).%26nbsp%3B%20Now%2C%20as%20you%20add%20in%20column%20B%2C%20the%20formula%20will%20populate%20in%20column%20A.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20attach%20a%20file%20as%20an%20example.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20more%20useful%20tips%20at%20%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%3C%2FA%3E%2C%20with%20free%20downloads%20etc.%20at%20%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%2Fthought%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%2Fthought%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ddelise
New Contributor

Hi, I'm trying to simplify a list. I have two columns to work with in this scenario. I want Excel to autofill column B if column A says XXX.

 

For example: if column B = London, I want column A = United Kingdom.

 

I originally though I could use the vlookup function, but I believe that only allows the destination to be one cell. I want Excel to auto fill the next column over if the column next to it says a certain word. This seems like an easy, task, but I can't find a solution.

1 Reply
Highlighted

@ddelise 

I am not quite sure why you don't auto-fill, but the best idea would be to put your data in a Table.

 

For example, if you have the word 'Country' (or whatever - the UK isn't a country I know before anyone has a go at me) in cell A1 and city in cell B1.  Then, put your VLOOKUP formula in cell A2 and your city in cell A2.

 

Now , highlight cells A1:B2 and Insert->Table (CTRL + T).  Now, as you add in column B, the formula will populate in column A.

 

I attach a file as an example.

 

You can find more useful tips at www.sumproduct.com, with free downloads etc. at www.sumproduct.com/thought .

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies