Importing from Excel into monday.com: advanced techniques to manage complex spreadsheets

Importing spreadsheets into

Categories

smartmockups_l2akogmv
FREE EBOOK!

The Complete Guide to Getting Your Team to Adopt and Succeed With monday.com

Recent Posts:

What’s new on monday. com: July 2022

Stay on top of monday .com’s newest features with our monthly review  At Lucid Day, it’s our goal…

Case Study: How Lucid Day streamlined processes for accounting firm using monday.com

Czarnowski & Beer LLP is a leading firm with over 35 years of experience providing financial services for…

CONNECT WITH US

Instagram Icon and Link

Importing Excel spreadsheets or Google sheets into your monday is one of the most common tasks you’ll do as a manager. That said, it’s not always easy or intuitive to figure that out by yourself. The great part about monday is that it makes doing some complex tasks with those Excel files relatively easy. 

Also check out our Excel tutorial,  Importing Excel 101. This post will cover some of the more advanced techniques you’ll use when integrating Excel into your monday. 

We’ve got everything you need to make sure that you’re ready, able, and willing to monday like the rockstar we know you are. Here’s what you need to know.  

Challenge 1: Pulling multiple Excel tables on one sheet into monday.com

Sometimes you’re going to have multiple tables within a sheet. In the example below, we have two different tables of customer information and a task list all within one sheet. That’s three different groups for monday. This is a pretty common type of situation, either because those tables are related in some way, or you need all that information at once. 

Unlike Excel or Google sheets, monday doesn’t work in this way. However, monday can keep all of this information easily accessible and actionable. You’re just going to have to finagle it a little. Here’s how.

Figure out how you want to view the data

Sometimes it’s easiest to have separate boards for separate tables. If you plan to use separate boards, then go on to the next step. If you want all those tables in the same place, on one board, skip to Option 2. 

Option 1: Separate boards: one board per table

Unfortunately, you can’t just import all the tables at once from one Excel sheet into monday if you need each table to have its own board. Trying to do this from one sheet creates a mess and monday will be confused. To do this successfully, it’s best to copy each table to a unique sheet first, then upload them to monday.com separately. 

Option 2: One table, separate groups

This is easy. You’ll actually import the entire sheet into monday. Here’s how: 

First, save your sheet. if you have multiple spreadsheets in a book, you’ll make sure the sheet you want to move is in the first position. 

Next, import your spreadsheet into monday. It’ll import into one long group, and you’ll use a few different techniques to separate it out. You’ll see that any space that’s unnamed will stay in monday, and that’s a great way to indicate the separation between groups. From there you’ll be able to batch select and create the task list you need. 

Option 3: One table, all one group

If this sounds like the best option for your data, you’ll want to create different columns for all the tables that are located below your first table before importing to monday.com. Basically, you’re creating one long table by adding columns. 

The item names SHOULD REMAIN THE SAME, even if the item information is changing – or being added to. Once you have it formatted this way, you should be able to easily import it into monday and make the changes you need to have it all be in one group. 

Challenge 2: How to create a relational database in monday by importing your spreadsheets

This is a fairly new and helpful feature that’s worth highlighting so that you can really understand how it works. The relational database can be used to manage related entities – clients and accounts, properties across boards, inventory and related parts, you name it. Anything that may show up more than once on different boards can be automatically connected.

For example, in a contact, you have the name of the account as your item, the address, and then the contact info. Normally, if you import this sheet exactly as is, you’d get multiple listings for any account that is listed more than once.The relationship database will help avoid that. 

In our sheet you could see that Bakers Dozen LLC would have three separate listings. The challenge is that we have three separate contacts within that organization, and we don’t want multiple listings for it. Here’s how you can have one listing while still creating space for multiple contacts. 

Step 1. Import your account information

First, you’re going to import everything from the account information column. Create a copy of your sheet and then use that one to remove the duplicates. Go to your data, and remove the duplicates from column A. Now your account information is ready to be imported to monday. 

Import the data into monday just like you normally would. You’ll also be able to pull in the email column. 

Step 2. Import your contacts

Move your contact information, both contact and contact email, to the front of the sheet. Then remove the address and general email columns. You’ll just need the account name and the other two columns we mentioned.

You’ll import the data then and create the structure for your board. 

Step 3. Create a connect-to-board column on the board.

First create a connect-to-board column. 

Step 4: Go to automate and search for “match”, create an automation to link your boards together. 

Then, you’ll choose the item creation in automations. Choose “when an item is created in this board connect the item where this column matches this column in another board by this logic.” 

You’ll need to make sure your automation ends up saying this “When an item is created in this board, connect the item where account information matches name in Accounts by this logic.” 

You’ll then duplicate the group, and it will automatically link those groups together thanks to your automation. 

Now you have a relational board that has all the information you need. It should have all the contact info for the different organizations. It automatically links. You’ll save a bunch of time, you can see what other contacts link to it. 

This IS a hefty thing to do. If you have a ton of items you’re importing, consider chunking it up into more manageable batches of data. Double-check your work, and make sure that your filter is working correctly, and find any stragglers. Got it? Great. Go forth and monday!

Looking for more help with monday.com? Subscribe to our mailing list today!

Share:
https://lucidday.com/wp-content/uploads/2020/12/cropped-Lucid-Day-Logomark-3.png

Have more monday.com questions?
We’re here for you.

Lucid Day grew out of a passion for making work operations spark - for the individual, the team, and the business as a whole. We drive change in our customer’s business by focusing on what will drive effective teamwork. We establish intuitive, simple, and robust workflows in monday.com within an effective structure to drive. And we believe monday.com can provide that unleashing energy.

As small and mid-sized business owners and loyalists, we’ve experienced first-hand the power of monday.com to revolutionize the way we do business and want other SMBs to experience it too. No matter the size of your organization or your budget, we will find a way to help you get all you can out of monday.com.