Excel Custom Lists: How They Work Across Workbooks & PCs

Excel custom lists let you define your own fill sequences (like department names or product codes) and reuse them across different workbooks and computers. This guide explains how custom lists are stored, how to move them between PCs, and how to keep them working when you share files.

What You’ll Need

• Microsoft Excel 2016 or later (Windows or Mac). Steps are similar in older versions.
• Access to the Windows Registry (only if you want to manually export/import lists on Windows).
• Administrator rights on your computer if you need to modify the Registry.

Step-by-Step

1. Create a Custom List

1. Open Excel and go to File > Options (Windows) or Excel > Preferences (Mac).
2. Select Advanced (Windows) or Custom Lists (Mac).
3. In the Custom Lists box, you’ll see built-in lists (like days and months). To add a new one, type the entries in the List entries box, one per line, or select a range from a worksheet.
4. Click Add and then OK. Your list is now saved in Excel’s settings.

2. Use a Custom List in Any Workbook

1. Type the first entry of your custom list in a cell.
2. Drag the fill handle (the small square at the bottom-right of the cell) down or across. Excel will continue the sequence using your custom list.
3. You can also use the Fill > Series command and choose AutoFill to use the list.

3. How Custom Lists Travel Between Workbooks

1. Custom lists are stored in Excel’s application settings, not inside the workbook file. So when you open the same workbook on another computer, the custom list is not automatically available there.
2. If you only need the list for a one-time fill, you can copy the values from the original workbook and paste them into the new one. The fill will work as long as you have the list defined on that machine.
3. To make the list available on another computer, you must export it and import it on the destination PC (see next steps).

4. Export Custom Lists on Windows (Using Registry)

1. Close Excel.
2. Press Win + R, type regedit, and press Enter.
3. Navigate to one of these paths depending on your Excel version:
   • Excel 2016/2019/365: HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
   • Excel 2013: HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Options
   • Excel 2010: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
4. Look for a string value named LIST (or LIST1, LIST2, etc.). Each one contains a custom list, with entries separated by commas.
5. Right-click the value and choose Export. Save the .reg file to a USB drive or cloud storage.
6. On the new computer, double-click the .reg file and confirm the import. Restart Excel.

5. Export Custom Lists on Mac

1. On Mac, custom lists are stored in the Excel preferences file. There’s no simple export feature.
2. You can copy the file com.microsoft.Excel.plist from ~/Library/Preferences/ to the same location on the other Mac. This will copy all Excel preferences, not just custom lists.
3. Alternatively, you can recreate the list manually on the other Mac using the steps in section 1.

6. Share a Workbook with Custom Lists

1. If you send a workbook to someone, they won’t see your custom list unless you also send the list definition.
2. You can include the list as a hidden sheet in the workbook and instruct the recipient to import it (via File > Options > Advanced > Custom Lists and selecting the range).
3. Or you can use a named range and a data validation dropdown instead of a custom list – that works across computers without any extra setup.

Common Mistakes

• Thinking the list is saved in the workbook. It isn’t. Always export/import or recreate.
• Using commas in list entries. When you export to the Registry, commas separate entries, so an entry with a comma will be split.
• Editing the Registry without backing it up. Always export the entire Options key before making changes.
• Forgetting to close Excel before editing the Registry. If Excel is running, it may overwrite your changes on exit.
• On Mac, copying the plist file while Excel is running. This can cause corruption.

Tips

• Use the LIST registry value to back up all your custom lists at once – export the whole Options key.
• If you frequently move between computers, consider using a cloud-based add-in or a personal macro workbook that recreates your lists on startup.
• For simple sequences (like months), the built-in lists are already available on every computer.
• Test your exported .reg file on a test machine before relying on it.
• Instead of a custom list, you can use a data validation dropdown that references a named range on a hidden sheet – this travels with the workbook.

Troubleshooting

• The custom list doesn’t appear on the other computer. The import didn’t work. Check that the .reg file was merged successfully (you should see a confirmation message). Also ensure the correct Excel version path was used.
• Excel crashes after importing the .reg file. The list may contain invalid characters or commas. Open the .reg file in Notepad and check the data. Remove any commas within entries and re-import.
• The list works in one workbook but not another. That’s expected – custom lists are global to the application, not per workbook. If it doesn’t work, the list may not be defined on that computer.
• On Mac, the custom list is missing after copying the plist file. Make sure you copied the file to the correct location and that Excel was closed during the copy. Also, the plist may be cached – try restarting the Mac.
• The fill handle doesn’t use your custom list. Make sure you type the first entry exactly as it appears in the list (case and spaces matter). Also, the list must be in the same order you want the fill to follow.

Frequently Asked Questions

Q: Can I store a custom list inside a workbook so it opens on any computer?
A: No, custom lists are stored in Excel’s settings, not in the file. You can embed the list as a hidden sheet and recreate it on the other machine.

Q: How many custom lists can I have?
A: Excel allows up to 200 custom lists (including built-in ones). The exact number may vary slightly by version.

Q: Can I use a custom list in a data validation dropdown?
A: Yes, you can reference a custom list in a data validation rule by typing the list name (e.g., =MyList) in the Source box. However, this only works on the computer where the list is defined.

Q: Will my custom lists sync across devices if I use Microsoft 365?
A: No, custom lists are not synced via the cloud. They are stored locally on each device.

Q: Can I export custom lists on Mac using a similar registry method?
A: No, Mac doesn’t have a registry. You can copy the preferences plist file, but that replaces all Excel settings.

Q: What happens if I edit the Registry incorrectly?
A: You could corrupt Excel’s settings. Always back up the registry key before editing, and only change the LIST values.

Related

• Error 0x80070715 on Windows 10: Fixes
• PhotoshopCS6Portable.exe: Safety & Windows Error Fixes
• Google Authenticator Setup in Microsoft: Step-by-Step