Introduction

If you’re a YouTube user, staying updated with content can be time-consuming, especially if you follow multiple channels. Automating this process using Excel allows you to track new video uploads efficiently. By leveraging Power Query, we can extract YouTube RSS feed data, transform it into a structured format, and integrate it into an Excel dashboard. This post walks you through the steps to achieve this and reviews the final Power Query M-code approach for a streamlined process.

What You Need to Get Started

To import YouTube RSS feeds into Excel, you need:

A YouTube Channel RSS Feed URL:

• Format: https://www.youtube.com/feeds/videos.xml?channel_id=CHANNEL_ID
• Example: https://www.youtube.com/feeds/videos.xml?channel_id=UC_x5XG1OV2P6uZZ5FSM9Ttw
• To find the Channel ID, visit the YouTube channel, inspect the page source, and search for channelId=.

Microsoft Excel (2016 or later)

• Power Query is built-in from Excel 2016 onwards. Earlier versions require the Power Query add-in.

Basic Understanding of Power Query

• Familiarity with data transformation and table manipulation in Excel will be helpful but is not required.

Step-by-Step Approach Using Power Query M-Code (Do It Yourself)

This approach follows a structured workflow, making the imported data cleaner and more readable in Excel.

Step 1: Define YouTube Channel Information

• Store static details such as the Channel Name, Channel URL, and RSS Feed URL in Power Query variables. This improves reusability when working with multiple channels.

Step 2: Load the RSS Feed into Power Query

• Use Web.Contents(RSSFeedURL) to fetch the XML data.
• Convert XML into a table format using Xml.Tables.

Step 3: Extract Video Data

• YouTube’s RSS feed stores videos in an entry table. We extract essential details:
• title (Video Title)
• published (Upload Date)
• id (Unique Video ID)

Step 4: Construct the YouTube Video URL

• Extract the unique video ID and append it to https://www.youtube.com/watch?v=.

Step 5: Format and Structure the Data

• Convert the Published Date into a proper date format for sorting and filtering.
• Rename columns to be more readable.
• Remove unnecessary fields (e.g., id).
• Reorder columns for clarity.

Step 6: Load Data into Excel

• After applying transformations, Close & Load the data into an Excel table.
• The table updates dynamically when refreshed, fetching the latest video uploads.

Better…Have ChatGPT Create The Power Query M-Code for You

If you want to automate the M-Code creation process, you can use ChatGPT to generate a Power Query script for your YouTube RSS feed import. Below is an M-Code that I used ChatGPT create (edited by me) that achieves the same outcome:

let
    // Step 1: Define YouTube Channel Information
    ChannelName = "Excel Off The Grid",
    ChannelURL = "https://www.youtube.com/@ExcelOffTheGrid",
    RSSFeedURL = "https://www.youtube.com/feeds/videos.xml?channel_id=UCTx8QvRuF3BXTe5OIaVF9OA",

    // Step 2: Load RSS Feed from YouTube
    Source = Xml.Tables(Web.Contents(RSSFeedURL)),

// Step 2: Load RSS Feed from YouTube
    Source = Xml.Tables(Web.Contents(RSSFeedURL)),

    // Step 3: Navigate to the "entry" table (which contains video data)
    Entries = Source{0}[entry],

    // Step 4: Extract relevant columns (Title, Published Date, and Video ID)
    SelectedColumns = Table.SelectColumns(Entries, {"title", "published", "id"}),

    // Step 5: Generate Correct YouTube Video URLs from Video ID
    AddVideoURL = Table.AddColumn(SelectedColumns, "Video URL", each "https://www.youtube.com/watch?v=" & Text.AfterDelimiter([id], ":video:")),

    // Step 6: Format Published Date
    FormattedDates = Table.TransformColumns(AddVideoURL, {{"published", each DateTime.FromText(_), type datetime}}),

    // Step 7: Remove unnecessary columns
    CleanedTable = Table.RemoveColumns(FormattedDates,{"id"}),

    // Step 8: Reorder columns for better readability
    ReorderedColumns = Table.ReorderColumns(CleanedTable,{"title", "published", "Video URL"}),

    // Step 9: Rename columns for clarity
    FinalTable = Table.RenameColumns(ReorderedColumns, {
        {"title", "Video Title"},
        {"published", "Published Date"}
    })

in
    FinalTable

Steps Performed in the Code

1. Fetches YouTube RSS feed data using Web.Contents.
2. Converts the XML structure into a table format.
3. Extracts essential columns (title, published, id).
4. Generates YouTube video URLs dynamically.
5. Converts the Published Date column into a proper date format.
6. Removes unnecessary columns to keep the dataset concise.
7. Reorders columns for logical readability.
8. Renames columns for better clarity and ease of understanding.
9. Loads the structured table into Excel, ready for dashboard integration.

Benefits:

• Automated Code Generation: ChatGPT simplifies the process by generating Power Query M-Code, reducing manual effort.
• Customizable: Users can modify the script to track multiple channels.
• Dynamic Updates: Refreshing the table retrieves the latest videos without additional work.
• Optimized Data Structure: The script provides a structured and clean dataset for Excel-based reporting.

Review of the Final Power Query M-Code Approach

The Power Query M-code implementation effectively extracts, cleans, and presents YouTube RSS feed data in Excel. Here’s a summary of its advantages:

Automation

• No manual tracking of new uploads — simply refresh the table.

Structured Data Processing

• Extracts only relevant information (Title, Date, Video URL) for a concise dataset.

Clickability

• Automatically generates direct video links for easy access.

Flexibility

• Can be modified for multiple YouTube channels or further refined with additional transformations.

Compatibility with Excel Dashboards

• Supports PivotTables, charts, and conditional formatting for enhanced insights.

Conclusion

Integrating YouTube RSS feeds into Excel using Power Query is an efficient way to track new video uploads and maintain an up-to-date content repository. By following this approach, you can automate data retrieval, ensuring accurate and structured insights without manual intervention. Whether you’re monitoring a single channel or multiple ones, this method provides an automated, scalable, and user-friendly solution.

This was a amazing little project that, utilizing ChatGPT, was simple to create, modify as needed, and implement.

Give it a go yourself!

About Don

“It’s time for different”

Connect with me!

LinkedIn, Flipboard, Twitter, Snapchat

Import YouTube RSS Feed(s) into Excel Using Power Query 👏 was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Process

Prior to commencing this, I created three Named Ranges for the data set in Excel.

• pt_datasource — the range housing all of the values.
• pt_rowsource — the far left column reflecting Product names.
• pt_columnsource — the top row reflecting the month-end dates.

These are then referenced in the request to ChatGPT.

Here’s what I asked it to do:

1️⃣ “Create an Excel formula that extracts a value from range named “pt_datasource” where the value is in row 10 and column 10”

For step 1, I used row and column placeholders just to see what it returned.

Response? — The formula would be:

=INDEX(pt_datasource,10,10) ✔️

Perfect. An INDEX function for this makes perfect sense.

Next, let’s clean it up by automating the row and column lookup depending on cell variables I created.

2️⃣ “Modify this formula to replace the column and row values with an automatic lookup ability. The row match looks at cell B5 and finds the appropriate row using a range named “pt_rowsource”. The column match looks at cell C4 and finds the appropriate column match using a range named “pt_columnsource”.

Response? — The formula would be:

=INDEX(pt_datasource,MATCH(B5,pt_rowsource,0),MATCH(C4,pt_columnsource,0)) ✔️

Perfect. Using the MATCH functions to accomplish the 2-way lookup is the best way to go.

I then pasted it into my worksheet to test it. No problem. Worked like a charm.

This post was originally posted on LinkedIn — follow me on LinkedIn and follow #twinztalk as well!

SUMMARY?

I was able to write an normally complex INDEX/MATCH function by just explaining what I wanted to do. 💪💯

NO explaining that I wanted an INDEX MATCH formula.

#chatgpt may not be perfect, but it’s good enough to make a serious productivity leap in my Excel spreadsheet models development.

And that’s HUGE for me.

About Don

“It’s time for different”

Connect with me!

LinkedIn, Flipboard, Twitter, Snapchat

Microsoft Excel — ChatGPT — The easiest way to write a complex formula? was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

Use ChatGPT to automate printing worksheets to PDF

Leila Gharani, Excel MVP, recently shared a great video on how you could use ChatGPT to augment your work in Microsoft Excel.

There is a ton of opportunity to take your productivity to another level by taking advantage of this new artificial intelligence chatbot.

In that video, her last example was how to create an automation routine to print a worksheet, selected via a Dropdown list, that will automatically

👉 generate a PDF

👉 Name the PDF the same as the worksheet name, and

👉 Save it to the same folder the workbook is in.

Even if you have never used macros, you can do this.

EVERY professional can use this technique. Just do it! Check out that tip in the video below!

In case your interested, the full list of topics in Leila’s video are:

1️⃣ 00:00 How to Use ChatGPT with Excel

2️⃣ 00: 52 Write Excel Formula with ChatGPT

3️⃣ 05:06 Data Validation

4️⃣ 08:42 Nested IF Formulas with ChatGPT

5️⃣ 12: 42 Writing VBA Code

6️⃣ 17:14 Wrap Up

Leila, thanks for the video and, in particular, this one fantastic example. Well done!

This post was originally posted on LinkedIn — follow me on LinkedIn and follow #twinztalk as well!

About Don

“It’s time for different”

Connect with me!

LinkedIn, Flipboard, Twitter, Snapchat

Microsoft Excel — An #excel automation tip EVERY professional should use! was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

Whenever analysis and reporting is performed in Excel — and practically every company does — it is imperative that data is aggregated correctly and “transformed”.

For analysis to be efficient and effective, proper structure of the data and the development of additional fields (to aid analysis) is very important.

If interested, you can get a download of the Excel file I walk-through below.

The two charts above were developed for a recent training session I conducted.

A part of the session was to focus on working with a data set I compiled (Apple historical sales) and performing basic data “shaping” to enable flexible analysis and reporting.

Below, I highlight the steps taken to do this and touch on three areas of Excel that are typically used for basic data transformation and analysis:

• Excel Data Tables
• VLOOKUP function
• Pivot Tables (future post…) — Part 2

Data Transformation / Shaping Process

Excel Data Tables

Here is a excellent summary from TechRepublic of key reasons you want to use Excel Tables. Key considerations mentioned include:

• Effortless data entry
• Always visible headers
• Formula autofill (my favorite!)
• Dynamic range

Step 1-Convert raw data input into an Excel Table

From this —

To this —

Step 2 — Create additional fields in the data table

This will enable the analysis we are potentially interested in getting. In this session, I have anticipated a need for three additional fields.

• (1) — Calendar year (Apple reports on a fiscal year). Why a calendar year? It is likely that when data analysis begins, information on a calendar year basis might be useful. The YEAR function is used for this.
• (2) — Category Summary. This field will “condense” Apple’s classification (which changes over the years) to a more summary grouping. This will enable higher level insight which would not be available from the raw dataset. The VLOOKUP function, with EXACT match, is used.
• (3) — Stratification of the Amount field. This may be useful to report aggregated data and develop charts regarding frequency of occurrence. The VLOOKUP function, with APPROXIMATE match, is used.

Step 3 — Populate the Calendar Year field

• As soon as the formula is “Entered”, the Table populates the entire column immediately. This is known as “formula auto fill” and is a very useful reason to use tables — a lot.

Step 4 — Populate the Category Summary field

We will use a VLOOKUP with EXACT match to do this. Here is the formula syntax for VLOOKUP.

• Here is the flow, with the LOOKUP table referenced (which is located on another worksheet).

• The formula is “=VLOOKUP([@Category2],SummaryLookupTable2,2,FALSE)”
• Translation…Look at the Category2 field, go to the SummaryLookupTable2, if you find a match — return the value from column 2, and I want an EXACT match.

Step 5 — Populate the Stratification field

Getting this field populated is more complicated. Here’s why:

• It involves 2 lookup tables. Since the Amount field includes $’s AND Units, separate lookup tables are needed for DOLLARS grouping and UNITS grouping.
• Nesting functions is used to resolve this. The formula performs the following:

— Use the IF Function to check whether the TYPE field is “Dollars”.

— If TYPE = “Dollars”, then use the specified lookup table (result if IF statement is TRUE).

— If TYPE is NOT “Dollars”, then use the Units lookup table (result if IF statement is FALSE).

Our data set is complete…for now.

Next step is to start to build analysis and visualizations of the data. This is the fun part — and where pivot tables make it simple!

I will cover the basics tips I provided in the presentation in a future post…stay tuned!

About Don

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel — The BASICS of Data Transformation was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

If that applies to you, here is a simple Google Sheets → Excel PowerQuery combination to simplify and streamline that process for you.

If you would like a copy of the Excel file — with the PowerQuery import (with modifications), you can get it here.

Google Sheets

Set up Google Spreadsheet

• Set up a Google spreadsheet with the parameters you want. The image below highlights one example using the JM Smucker Company (SJM). Setting the symbol and Start Date as variables gives you the flexibility to change the symbol and generate the same data for a different company.
• So, I enter the symbol (“SJM”) and the Start Date (“January 1, 2000”). The end date is always today’s date — see image below.

• The TODAY() function is used to populate the end date.

• Now, create the Data Table (we use the =GOOGLEFINANCE function for this).

Here is the completed GOOGLEFINANCE function:

=GOOGLEFINANCE(a2, “all”, B2, C2, “DAILY”)

• Cell A2 = SYMBOL
• “all” requests all available historical information
• Cell B2 = START DATE
• Cell C2 = END DATE
• “DAILY” — I want information returned on a DAILY basis (weekly, monthly are other options — Daily provides the most flexibility for analysis purposes)

Prepare Data to be Queried by Excel (PowerQuery)

1 — “Publish” the Google Spreadsheet

Next, I will publish the Google sheet so that I can import the data into Excel (using PowerQuery).

Microsoft Excel

1 — Import Table into Excel

Now, we are ready to import data into Excel!

• Access the PowerQuery add-in and select “Get External Data” → “From Web”

• Paste the URL link copied earlier into the dialog box that appears.
• Next, select Table with data and select Edit to modify query. This is where we SHAPE the data in format we want for Excel (and analysis purposes).

2 — Edit Query to Shape Data Table

• Final modified query — and data load into Excel

Now, we are ready to perform some data analysis…and the fun starts!

Final Notes

• Updating the Google Sheet — whenever you open the Google Spreadsheet, the GOOGLEFINANCE function updates the data table. Do this step so that Excel imports the most current data.
• Want a different Company symbol? Simply change the symbol to whatever you want. (Make sure you EDIT PowerQuery to show proper symbol in imported table!)
• Need a longer (or shorter) period of time? Change the beginning date cell — that’s it!
• This process can be easily modified for multiple symbols — allowing peer group or industry analysis!

About Don

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel: Basic PowerQuery — Stock Price Data From Google Sheets was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

I have found myself having this discussion a lot recently as I present on the topic of effective use of Excel. It seems intuitive that we would take the effort to streamline steps, but few actually do.

If you are a user of Excel, you WILL benefit from setting up and using your Quick Access Toolbar!

Here’s everything you need to know — from basic to more advanced topics!

The Basics of the QAT

My previous post — “Microsoft Excel — The Basics of the “QAT”” — covers getting started.

• Placement of the QAT Toolbar
• Adding frequent tasks to the Toolbar
• Going a little deeper…

Microsoft Excel — The Basics of the “QAT”

The following YouTube video from the ExcelIsFun Channel also provides an excellent introduction.

Adding Custom Items to Your QAT

As you get more comfortable defaulting to your toolbar and NOT using the Ribbon commands, you will likely want to get a more advanced in the capabilities that you have access too.

Adding items from your QAT from your Personal Macro Workbook or custom add-ins that you have installed can super charge your productivity.

My Medium post “Microsoft Excel — Productivity with Add-In’s and the Quick Access Toolbar” takes you step by step to installing a custom add-in that I developed (request download here) and setting up shortcuts on the QAT for quick access.

Microsoft Excel — Productivity with Add-In’s and the Quick Access Toolbar

You can also set up macros in a Personal Macro Workbook that can be added to the QAT.

When you record a macro, select “Personal Macro Workbook” as the destination.

Once you do this, any macro in this workbook will be accessible from any workbook you are in.*

*Your Personal Macro Workbook is a hidden workbook that opens anytime you open Excel, and it runs in the background.

If you are interested in learning more about the Personal Macro Workbook, I cover it (on page 8) in a presentation you can download here.

If you have not yet started taking advantage of the Quick Access Toolbar, start now!

Good luck and I hope this gives you a good start!

About Don

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

One lesson learned over the years is that all of us, regardless of organization type or size, struggle with similar issues — primarily information management, organization, presentation, and effective use of our time. Let’s change that…one person at a time!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel — Step 1, Set Up Your Quick Access Toolbar was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

You can request the Excel file here!

This morning, I was reviewing Flipboard and came across this post by Mashable.com.

Black Friday 2017 deals from all over the internet, compiled just for you in one handy spreadsheet

For anyone interested in Black Friday sales — and staying on top of what is best deals are — the spreadsheet they created is a phenomenal resource!

The spreadsheet was compiled in a Google Spreadsheet as reflected here:

This is Where PowerQuery Comes In…

This is a wonderful resource as is, but not quite as flexible or user friendly as I would like.

Using the Excel PowerQuery add-in, I import the data into Excel and quickly shape it to get me this:

The magic of PowerQuery is I have linked this data to the web data provided by Mashable. So, whenever the data is updated, this spreadsheet will be updated simply by refreshing the query!

Not Quite Done Yet!

Considering the user, I want to provide a little bit of automation to the interface to make it friendly to use.

Here’s what was provided:

• Direct link to article
• Search box to search the “Deal” field
• Use conditional formatting to easily highlight and filter content — buttons provided to make use simple
• “Print Report to PDF” button (sorry, links won’t work in this PDF version)
• Count field so I can see how many items are currently displayed (updates with filtering)

Now to Test It

What are Deals on Kindle Devices?

• Enter “Kindle” in search field

• Rows containing “Kindle” are highlighted. Click “Filter Color” button.

Interested in TV (or related) Sales?

• Enter “TV” in search field, click “Filter Color” button

Want the Excel file? It includes the Power Query and macro code for your review and use. Get it here!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel — The Simplest Way to Understand PowerQuery was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

Why Did I Do This?

BitCoin is a HOT topic these days.

I was having a conversation with an executive this morning about BitCoin activity over the last few years. We were curious about what has actually transpired.

So I put this together using Excel and Power Query.

It took me 20 minutes from start to finish. And every time I open the Excel file, a simple query “refresh” will update my report.

It will take longer to write this post…

Here’s How I Did It

First, do you want a PDF of the report? You can download it here.

Here are the steps to make this happen:

Source the data

• Search for “Bitcoin price history” and found this Wikipedia page. It has the table with the data I want.
• Open Excel, access the Power Query add-in, and select the web as my data source. Paste the URL.
• Open query in Edit Mode.

Editing the DataSet

The Query, in edit mode, is shown below. The key steps were:

• Duplicate the “DatePeriod” column so I can extract the Year value.
• Split the newly created “DatePeriod” column and create, from that, a “Year” column.
• Add an “Index” column, sort it in DESCENDING order, and then remove the column (I do this because I want the most recent records on top — and no other fields would sort correctly).
• Load the query as a Table in Excel. Done with Power Query!

Prepare the Excel Worksheet for easy use

• Include a Hyperlink back to the table source data
• Macros added and assigned to buttons which 1) Refresh the Data Table and 2) automates printing and naming worksheet to a PDF formatted report (below).

If you would like the Excel workbook with the Power Query and the macros used to develop this, you can request it here.

Thanks for reading! I hope this demonstrates how a basic Power Query can be very helpful, with very little effort!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel — #BitCoin and Power Query was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

What’s The Problem To Be Solved?

A project I am working on entails looking at a selected portfolio of equities and creating a basic automated dashboard.

The “summary” page section that I want to automate is shown above.

What I need is a LOOKUP list to create a Dynamic Hyperlink. So I created a “MASTER” lookup list of over 5,200 stock symbols (the selected equities COULD BE any group of stocks — might as well cover all possibilities!).

Using Excel’s CONCATENATE capabilities, this took about three minutes.

Without the ability to use CONCATENATE, I would not be able to effectively — timewise — complete this task.

Here’s How It Is Done

• Start with a list of symbols in Excel — set up the list as an Excel Data Table. This list was sourced from the NASDAQ website using Excel Power Query to retrieve it (more on that later…).

• I picked four website links I want for each security (SEC Reporting, Reuters Business news, Google Finance page, and SeekingAlpha page). These are four very useful sites to quickly get more insight and information on a Company if the need arises.
• A sample URL for each website is reflected below:

https://www.sec.gov/cgi-bin/browse-edgar?CIK=AAPL&owner=exclude&action=getcompany

https://www.reuters.com/finance/stocks/company-news/AAPL

https://finance.google.com/finance?q=AAPL

https://seekingalpha.com/symbol/AAPL

Notice in each URL, the only variable that likely changes is the stock symbol. So if I can automate replacing that symbol in each URL, I can make quick work of the list of 5,200 symbols.

CONCATENATE Saves The Day

The CONCATENATE function in Excel provides the ability to connect strings of text into one text string.

Let’s use the SEC link above as an example:

• Break the URL string into 3 components — isolating the stock symbol, so I can automate that piece.

https://www.sec.gov/cgi-bin/browse-edgar?CIK=

AAPL

&owner=exclude&action=getcompany

TIP: A shortcut to using the actual CONCATENATE function is to use the “&” symbol (it’s a shortcut and way easier!)

“&” operator to concatenate strings in Excel

In Microsoft Excel, & operator is another way to concatenate cells. This method come in very handy in many scenarios because typing the ampersand sign (&) is much quicker than typing the word “concatenate” :) — https://www.ablebits.com/office-addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/#concatenation-operator

So here we go:

The formula is here → =$H$2&[@Symbol]&$H$4

I should point out that the two text strings — beginning and end of the URL — must be an ABSOLUTE reference. The cell address cannot change all the way down through the bottom of the list. The SYMBOL, however, is RELATIVE, and will pick up the cell directly to the left as it copies down the Table.

Once I put the formula in cell B2, it instantly updates all the way down through the end of the Table (one of my favorite features of Excel data tables!).

Now, repeat this process for the other URL’s and you have a full table that can be used to look up the HYPERLINK address.

Conclusion

This is a pretty unique example of how to use the CONCATENATE capabilities in Excel, but it is a real-application scenario which makes an (relatively) impossible task very easy!

I hope this helps you gain some understanding of how you can apply these capabilities to make your Excel work easier!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel: Using CONCATENATE To Create an Impossible Lookup Table in Seconds was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.

If you would like a copy of the Excel file showing the TRANSPOSE process I discuss in this post, you can download it here.

I rarely need the TRANSPOSE function, but when I do…

The Problem To Be Solved?

I have an Excel financial model I use to track and monitor stock holdings and stocks I am interested in watching. I previously discussed this concept here.

The hyperlinks above are populated by a Lookup tablethat is set up like this:

I want to “FLIP” the columns and row descriptions (Symbols across the top link sources for the rows), so it is set up like this:

Because I want to be able to create a simple list of links for each Symbol, like this:

Use TRANSPOSE to Do This

1 — Copy the Lookup Table

Select entire table, then select “copy” (CTRL + C):

2 — Create New Data Table

Go to a cell A1 on a new sheet (it can be anywhere — I prefer a new sheet).

Right mouse-click, select Paste Special → Paste Special.

Select “Values” → “Transpose”, and click OK.

Here’s the table.

3 — Copy and Paste The Stock Symbol References Where Ever I Want :-)

Conclusion

As you can imagine, this table conversion process takes seconds using TRANSPOSE.

It is not something you would frequently need (at least I don’t), but keep it in mind in case you do have a need!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Microsoft Excel: The Beauty of the TRANSPOSE Function was originally published in Let’s Excel on Medium, where people are continuing the conversation by highlighting and responding to this story.