Organize Your ESL Lessons and Reuse Them with Excel

by Ellen Baker Dubois · Published January 24, 2021 · Updated August 17, 2022

Teachers lean how to organize your ESL lessons with this cross-reference Excel spreadsheet

If you’re an English teacher you’re going to want to organize your ESL lessons so you can reuse them. I’m going to show you my system, where I put all my lessons into an Excel table with a link back to the file location. I classify each lesson by subject, skills practiced, type of document, and CEFL level. This way if I need a lesson on “writing a CV”, for example, I just use the ‘Find’ tool in Excel and search the whole table quickly by clicking “find next”.

Some teachers curate lesson ideas on Pinterest or with EverNote and I’ve tried both those methods but an Excel table is much more thorough in the search and much more precise.

Here’s my table as an example. You can see the categories: topic, location of the link or URL, document type, skills, grammar topic, and CEFR level.

Organize your ESL lessons just like I did in this Excel table. — Here’s a photo of my table. I’ll show you how to create your own.

I’m going to take you through the steps to make your own table or you can download a blank template. I’ll show you how to find a “local link” to one of your files on your computer, how to copy and paste a hyperlink into a cell, how to create a drop-down menu, and how to add hidden comments to a cell.

You can watch this video or scroll down to read the detailed instructions.

Use the “Find” button in Excel

So this is how the Excel table works. I just use the ‘Find’ button on the ‘Home’ tab. You can look for a business topic or grammar topic or just the word video.

Use the 'Find' tool in Excel to locate the link to your ESL lesson with just one word. — Just use the “Find” tool in Excel to locate a topic or grammar point.

In the photo below, I searched for “Personnel” and clicked find next. And I can see I have an article “Writing a job description”. I have a vocabulary list “Describing people in the office” and another vocabulary list about Offices and Personnel in general. I’ve also got a phrasal verb game. Do you see how that works? It’s very easy when you organize your ESL lessons in this way. It’s a little work upfront, but well worth it in the long run.

Use the 'Find' tool in Excel to locate your carefully categorized ESL lesson by topic or CEFL level. — Using the “Find” tool in Excel to locate a subject and keep clicking “Find next” until your satisfied with the result.

Practice setting up your own cross-reference table

Step 1: Let’s create a table for you. Open an Excel table. Type your title into Cell A1. Decide how many columns you will need in your table (mine is six columns). Highlight cell A1 and drag the highlight over to column F. Click “Merge and Center”

Learn to use the tool 'Merge and Center" for the title of your Excel table which you'll make to organize your ESL lessons. — In the “Home” tab, click “merge and center” to merge all the cells in the title.

Step 2 Write your column headers

Row 2 will be the headers for each column. So Column A row 2 is the Business Topic. Of course, you can organize your columns in any way that you like.

The second column B2 is going to be the location of the file either on your computer or in Google or on the internet. I like to have narrow columns when it’s possible so I use ALT+Return to have more than one line in the header. You can edit these multiple lines by using the little arrows on the far right.

The 'Alt+return' function in Excel will allow you to have narrow column headers with multiple lines in one cell. — Alt+Return will give you multiple lines in one Excel cell. Editi these lines by using the little arrows at the right to navigate up and down.

Finish labeling the rest of the column headers as in the photo below. Column C is going to be the type of document. Same thing here ALT+ENTER to make several rows. I like to look up videos, MP3 files, PowerPoint slides, Website activity, or a Word document. These choices are totally up to you.

Finished my title and column headers in my Excel table and now I'm ready to copy and paste and organize all the links to my ESL lessons. — Finish labeling all your columns.

Column D is titled Skills. Same thing again ALT+Return for several lines. We have reading skills, Writing. Listening. Speaking. Put whatever skills you’d like to have in there such as presenting.

Column E is the Grammar Topic. And Column F is the CEFR level. Use ALT+Return again for the next line. And this is going to be A1-C2.

Step 3: Insert a drop-down menu to speed up data entry

In column D for the Skills, I’d like to insert a drop-down menu instead of typing ‘reading’ every time or ‘speaking’ we can choose our skill from the menu. The is will saves you time.

Go to sheet number two where we will put in our options for the drop-down menu just like in the photo below. Since this is the Skills column, we’ll have the word ‘Reading’ in Cell A1, drop down to cell A2 for ‘Writing’, Cell A3 for ‘Listening’ and cell A4 for ‘Speaking’. And maybe a combination of two? Could be listening and speaking ‘L&S’ in Cell A5. And the combination Reading and Writing ‘R&W’ in cell A6.

So we’re ready to go back to Sheet Number 1.

Type choice of options for a drop-down menu in Excel in a column on Sheet2. This range of cells will be inserted used in your drop-down menu formula. — **Here are the drop down menu options for ‘Skills’ column.**

There are five steps to insert a drop-down menu into an Excel table. Drop-down meus are so practical because they reduce typing time. — **Here are the steps to insert a drop down menu under the ‘Skills’ Column**

On Sheet1, click in cell D3 where we want the drop-down menu with all the options for ‘Skills’. Click on the ‘Data’ tab and then ‘Data Validation’. We want to look for ‘List’ And then type in our formula in the Source box. =Sheet2!$A$1:$A$6 and click OK. Let’s see if that works? In the photo below you can see the drop down menu.

See what a drop-down menu looks like in Excel.

We’re also going to do another drop-down menu for CEFR levels. So let’s go to Sheet 2 and skip over to column C.

Here are the four options for the CEFR level drop-down menu found in your Excel table to organize your ESL lessons. — Here are the four options for the CEFR level drop-down menu found on Sheet2.

So our CEFR levels are: Cell C1 you are going to type ‘A1/A2 lower level”. Cell C2: Type ‘B1/B2 intermediate level’. Cell C3: Type ‘B2/C1 Upper levels’. And in Cell C4: Type ‘C1/C2 advanced’

You can be much more precise than this with your CEFR levels by typing in each level individually rather than combining them, but I find that being too precise is really not worth the effort. Here the drop-down menu is going to be found in C1 to C4. So let’s go back to Sheet 1.

Here are the steps to insert a drop-down menu under the column for CEFR Levels in your Excel table when you organize your ESL lessons. — Here are the steps to insert a drop-down menu under CEFR Levels.

On Sheet1, click in cell F3 where we want out the drop-down menu with all the options for ‘CEFR Levels’. Click on the ‘Data’ tab and then ‘Data Validation’. We want to look for ‘List’ And then type in our formula in the Source box. =Sheet2!$C$1:$C$6 and click OK. Let’s see how that looks. Great!

Drop-down menus in Excel are great to avoid retyping the same information. They will help you to organize your ESL lessons more quickly. — Drop-down menus are great when they work. I got the formula right!

Now, let’s copy the formula for the drop-down menu in the whole column. It’s just a little bit tricky because we have to use ‘Paste Special’ instead of the normal Ctrl+V. Go to the top cell D3 and copy the formula with Ctrl+C. Then holding down the shift key scroll down through column D to row 75. Right click on that highlighted column to find ‘Paste Special’. Voila! you have copied the formula for drop-down menus. Do the same in Column F ‘CEFR Levels’.

75 rows may seem like a lot but I’m sure you’ve collected that many lessons on your computer from your career as an ESL teacher. You can always add more later now that you know how to copy a formula and use ‘Paste Special’.

After you insert the formula for a drop-down menu, you'll need to copy and 'paste special' that formula into all the cells in the Skills and CEFR columns.

Step 4: Resize Column Headers and Title Go back to the ‘Home’ tab. Highlight Row 2. Change type size to 14pt and ‘Bold’ face. Highlight Row 1 where the title is and change the type size to 20pt. and ‘Bold’.

We’re done with setting up the table! Now let’s start filling it up.

How to copy a link to an ESL lesson stored on your computer

Let’s star filling up our table with local files from our computer. Follow the steps I used with files from my computer.

This is a photo of Windows file explorer on my home computer. Find a file from your computer for practice copying a 'local link' into your Excel table to organize your ESL lessons. — This is a photo of Windows file explorer on my home computer. Find a file from your computer for practice.

Go to your directory of ESL files on your computer. Pick any file and right-click. Then click on ‘Properties’. That’s where we will find the ‘local link’ which we will copy and paste into our Excel table. The photo below shows the link I want to copy under ‘Location’. So just copy that with CTRL+C

To get a 'local link' to a file on your computer, just right click on the file and click 'properties' way at the bottom. This is the 'Properties' window where you will find your link under 'Location'. Copy that link. — To get a ‘local link’ to a file on your computer, just right click on the file and click ‘properties’ way at the bottom. This is the ‘Properties’ window where you will find your link under ‘Location’. Copy that link.

In the photo below you will see that we are back in Excel Sheet 1. Click Cell B3 where we will enter our local link. Now click on the ‘Insert’ tab and then ‘Insert Hyperlink’.
Paste CTRL+V the link into the ‘Address’ bar at the bottom of the ‘Edit Hyperlink’ window. That same link will be automatically recopied to the top bar ‘Text to display’. Delete that link and write a short meaningful title instead. And click OK.

When we click in a cell to insert a hyperlink, this 'Edit Hyperlink' window appears. Paste your link into the bottom 'Address' bar and rewrite a short meaningful, title in the top bar "Text to display'. — When we click in a cell to insert a hyperlink, this window appears. Paste your link into the bottom ‘Address’ bar and rewrite a short meaningful, title in the top bar “Text to display’.

When we look at the Excel table in the photo below, we’ve got our video title, in blue and underlined which shows it’s a link. So ‘Flying a drone’ for me is a technology business topic. The kind of document is a video. Use the drop-down menu under ‘Skills’ to choose Listening and Speaking L&S. There’s no grammar in this video. The CEFL level is probably going to be a B1 so choose that from the drop-down.

Now's the time to start filling in your Excel table of ESL lessons. You've got your link, now add a Business topic, type of document, choose a Skill using the drop down menu, no Grammar topic in this video and choose the CEFR level with its drop-down menu. — Now’s the time to start filling in your Excel table. You’ve got your link, now add a Business topic, type of document, choose a Skill using the drop down menu, no Grammar topic in this video and choose the CEFR level with its drop-down menu.

How to copy a link from a lesson stored on Google Drive

Let’s find a file on my Google Drive. So go to Google where we are going to repeat the same process. In the photo below, you see we are in my Google Drive. I right clicked on an article to ‘Get Link’.

Here we are in Google Drive. Right click on the file of your choice, and click 'Get Link'. — Here we are in Google Drive. Right click on the file of your choice, and click ‘Get Link’.

The Google file sharing window will appear. These are your permissions to share a file. It shows “anyone with the link.” That’s good. It may show up “restricted” when you open it, but that’s okay we can fix that by clicking on that tiny down arrow to the right of ‘restricted and choose “Anyone with this link”. Now “Copy link” and go back to your Excel table.

This is the 'permissions window' of Google Drive. Make sure that "Anyone with this link can view" is showing. It might say "Restricted" but don't fear, you can click on the little arrow to the right and choose "Anyone with link". Now click 'copy link'. — This is the ‘permissions window’ of Google Drive. Make sure that “Anyone with this link can view” is showing. It might say “Restricted” but don’t fear, you can click on the little arrow to the right and choose “Anyone with link”. Now click ‘copy link’.

So now we’re going to paste that link into our Excel table. Click on cell B4. Click on the ‘Insert’ tab and ‘Insert Hyperlink’. The “Edit Hyperlink” panel will open (as shown below). Paste the Google link into the “Address” space at the bottom by simply using Ctrl-v. We’re going to change the title up at the top to “Productivity and Sleep”.

This is the "Edit Hyperlink" window in Excel. Paste your link from Google Drive into the "Address" bar at the bottom and write a short, meaningful title at the top. — This is the “Edit Hyperlink” window in Excel. Paste your link from Google Drive into the “Address” bar at the bottom and write a short, meaningful title at the top.

Fill in the rest of the row. For column A ‘Business Topic’, I’d say ‘Health’ is the topic. In Column C, type in ‘article’. And for Column C Skill, choose ‘Reading’ from the drop-down menu and the CEFR level Column F, let’s choose B1/B2 intermediate from the drop-down menu. How easy!

Here's your Excel table to organize your ESL lessons with a link pasted in from your Google Drive. — See how easy it is to copy and paste a link from a file in your Google Drive and fill in all the cross-reference information? We can see that ‘Productivity and Sleep’ is a Health subject, an article for developing Reading skills, there’s no grammar, and it’s for an intermediate level.

How to copy a link from the internet

Let’s copy a link from the internet exactly the same way. In Google Search look for a worksheet on Prepositions of time, for our example. Look in the ‘Images’ to find a worksheet that looks good. Click on it to open the website page. Copy this link. CYTL+C.

Go back to Excel table, click on cell B5. Click the ‘Insert’ tab and the Hyperlink button. Insert the link from the website at the bottom in ‘Address’. Shorten the title at the top. Fill in the rest of the row: the ‘Business subject’ is Grammar. What type of document is it? It’s a worksheet. The Skill will be ‘Reading & Writing’. The Grammar Topic is ‘Prepositions of Time’. And it’s probably an A1/A2 low level. Done!

Here's your Excel table to organize your ESL lesson with a link from the internet pasted in. — We copied and pasted a link from a website into our table and quickly filled in the other columns. Really easy.

How to insert a Comment in an Excel cell

Let’s learn how to insert a comment. Inserting a little comment in a cell will save a lot of space in our table. When you organize your ESL lessons, keeping your table clean and not too cluttered will help you find that perfect lesson more quickly.

As an example, for the article on ‘Productivity and Sleep’ it would be good to find an associated video on the ‘importance of sleep’. We can create a comment to remind us next time to find that video.

Insert a blind comment to add information to a cell without loading up your table with too much information. Your Excel table to organize your ESL lessons will stay readable. — Insert a blind comment to add information to a cell without loading up your table with too much information. Your Excel table will stay readable.

Inserting a comment is really easy. Go to the cell C4 where it says ‘Article’ and right click. On the menu find ‘Insert a comment ‘. A little box will appear for your comment. Type in “Find a video on the importance of sleep”. A tiny red corner will appear in the cell to show you that there’s a comment available. So this is a great way to reduce the amount of information in your table. Just insert a comment to give you some little extra information and it cleans up your table really nicely.

Download your Excel template

Download a pdf of this blog

I hope this article will be helpful to you. It will be a big job to go through all your files on your computer and Google Drive to finally organize your ESL lessons, but just think you will be ready to quickly find the perfect lesson you need. It’s also going to be so much easier to share files with your colleagues. I’d put the spreadsheet on your Google Drive so you can find a file and share it quickly from your phone.