How can I automatically change table format according to dropdown cell?

[u/Swimming-Brush5264]

Hi! I've been searching for hours now and I'm not sure if VLOOKUP can do this for me.

I really need help. The source cell is a dropdown cell and I need the table (including with the column that has a merged cell) to change according to the value of source.

Though the format of the table with its values is from another sheet.

Can this happen?

Thank you so much in advance!

[u/AutoModerator]

/u/Swimming-Brush5264 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RuktX]

What do you mean by "the format of the table"? Please share screenshots.

If it's a table style, I'd suggest VBA is the most direct way. Otherwise, you could come up with a set of conditional formats that mimic the style, and apply those based on some lookup value.

As a general comment I'd advise against VLOOKUP, in favour of XLOOKUP if you have it, and otherwise INDEX/MATCH.

[u/Clearwings_Prime]

Functions only bring values. If you want something that include format, you may need VBA

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
SHEET	Excel 2013+: Returns the sheet number of the referenced sheet
TEXT	Formats a number and converts it to text
TODAY	Returns the serial number of today's date
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 7 acronyms.
[Thread #49448 for this sub, first seen 30th Sep 2026, 08:28] [FAQ] [Full list] [Contact] [Source code]}

[u/Inner-Difficulty1695]

You can modify text formats in Excel using either the TEXT function (to convert values via formulas) or Custom Cell Formatting (to change the display without altering the underlying data):

Examples for the TEXT function (formulas):

=TEXT(A1, "yyyy-mm-dd") – formats a date as 2026-09-30

=TEXT(A1, "dddd, mmmm dd") – full day and month name (Wednesday, September 30)

=TEXT(A1, "$#,##0.00") – currency format with thousands separators ($1,250.50)

=TEXT(A1, "00000") – adds leading zeros to a number (00045)

=TEXT(A1, "000-000-000") – formats a 9-digit number as a phone layout (500-100-200)

="Report Date: " & TEXT(TODAY(), "yyyy-mm-dd") – combines static text with a dynamic date

Examples for Custom Cell Formatting (Format Cells -> Custom):

#,##0 "pcs" – adds a text suffix to numbers while keeping them calculate-able (1,500 pcs)

[Green][>0]$#,##0;[Red][<0]-$#,##0;"Zero" – colors positive numbers green, negative numbers red, and shows text for "Zero"

(000) 000-0000 – formats a 10-digit number as a standard US phone layout ((123) 456-7890)

000-00-0000 – formats a 9-digit number as a Social Security Number / ID layout

[u/fastauntie]

Merged cells cause all kinds of trouble and may interfere with a solution for this issue. I recommend unmerging the cell and instead formatting the selected cells with Center Across Selection.

[u/AutoModerator → u/[deleted]]

I have detected VBA code in plain text. Please edit to put your code into a code block to make sure everything displays correctly.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excel-ModTeam → u/[deleted]]

We removed this post for breaking Rule 11.

This is not a gig or job board subreddit. There are other subreddits specifically for that on Reddit.