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

@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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies