You’ll be asked to select the header row for your spreadsheet. This is the row that gives a description of what its column actually contains and makes it easier for you to reference the data in the column.
Then you are asked to select a column that contains unique information. This step isn’t strictly required, but it is useful when you start to get into more granular data control. Pro tip: If your data doesn’t have a unique ID column, it’s easy to add a column called “Unique ID” and sequentially number all the rows.
Once you complete these steps, you can see the linked data in the data panel. If you imported multiple sheets, you can change which data you’re viewing by using the drop-down menu.
3. Add data to specific shapes
From here, you can start associating data with specific shapes. The dynamic shape library is a great way to display data because these shapes will change based on the data that is associated with them.
For my quota attainment, I decided to use a dynamic progress bar. I like to attach my data to the shape before I set up all of my equations and conditional formatting for a shape. It’s more than possible to set up the formulas first, but I think attaching the data first makes troubleshooting a little bit easier.
One of my very wise engineering professors told me, “Emily, you need to work smart, not hard.” In this instance, it is easiest to get one shape formatted exactly how you want, then make however many copies you need and then simply drag and drop the correct data onto the shape.
Since I’m making a chart of quota attainment by month, I decided to start with January. You’ll notice when a dynamic shape is first put onto the page, it has some data fields intrinsically associated with the shape. The two highlighted places are where you can change the shape properties.
I usually pick the colors so I can make sure my dashboard falls within my color palette. (Much better.) I then pick all of the other information I would like to display underneath each month. I drag out and format a text box for month, % quota attainment, and a triangle that I will conditionally format to only be visible underneath the current month.
Here is the point where I will start adding the imported data. First I select all the shapes and group them into a single shape using the group function (Cmd+G or Ctrl+G). Now that I have a single shape, I drag the row of data that I want associated with that specific shape by clicking the row number on the left of the data tab and drop it onto the shape.
We’ve done it! We’ve linked the data! Now all we have to do is choose which data to display where.
4. Display shape data
Let’s start with the progress bar. First, click into the data panel for the dynamic shape. We have to set the minimum and maximum values of the progress bar. 0 is almost always the minimum, so we can leave that one alone. The maximum should be the upper limit of whatever data you would like to display.
I’m going to make the maximum my goal quota for the month. I know what you’re thinking: But Emily, my quota changes every month! Fear not, my friend. We can use a formula to automatically use whatever quota you have in your spreadsheet. Since the column in my spreadsheet that tells me my quota is called “Quota,” I will use the formula =parent.” Quota”. Note: Formulas are case-sensitive.
There are a few different ways to reference data in Lucidchart: See our Help Center for a tutorial on how to use formulas.
In this case, I used the “parent” reference because the shape data I’m referencing is associated with the group shape. I have to call the parent data. If I had dragged the data directly onto the dynamic shape, I could have used the “this” or “@” reference.
And now we have the maximum all set!
Now it’s time to move on to the value that we want to display.
Now, if we were only doing one month, it would be exactly the same as setting a maximum, just substituting the “Quota” for “Closed.” However, since it will be a full calendar year of data, and we want the months not to give an error message or do anything funky, we’re going to do something a little fancy: We’re going to write *gasp* an if statement.
What are the two options for our if statement?
- If the month is in the future, we know there is no data in the spreadsheet about quota attainment for that month. So if the cell is empty, we want the quota to display 0.
- If there is quota data in the cell, we want it to display that data.
The way if statements work is your first pass in which statement you’d like evaluated. In this case, we want to know if there is any data in the “Closed” column associated with this shape. You then pass in what you would like to happen if the statement is true, followed by what you would like to happen if the statement is false.
So, in this example, our if statement will look a little something like this:
=IF(ISEMPTY(parent.”Closed”), 0, parent.”Closed”)
We first ask if the cell is empty (ISEMPTY(parent.”Closed”), and say if it is empty we want to go ahead and say the value is 0, and if it isn’t empty to display how much was closed that month. Now we can see that thanks to my random number generator in the spreadsheet, I met my quota for January.
Next, I’m going to make sure the month and quota % are displayed below the progress bar.
The way we can do this is to make a custom data field by clicking the little plus icon on the right side of the custom data bar. Now we need to pull the monthly data from the parent shape, just like we pulled the data for the progress. Since the column that contains the month information is called “Month” in the spreadsheet, the function is =parent.”Month”
Now that I have the correct month associated with my shape, we get to display it. Luckily, it’s very easy—you just have to click the +T button on the right when you hover over the shape data.
And now we have the correct month below the progress bar!
Now, we do the same thing, but with the quota percentage. Since Lucidchart can do most mathematical operations, I’m going to do the percentage calculation in the formula bar. Since we’re going to run into the same empty problem we did with the quota display, we’re going to write another if statement.
The first part of this if statement is the same as the last if statement: We want to know if the cell is empty or not. If the cell is empty, I want it to display a dash to indicate that the month hasn’t happened yet. If the cell is not empty, I want it to display the percentage of quota attained. When we put all that together, we get:
=IF(ISEMPTY(parent.Closed), "-", ASPERCENT(parent."Closed"/parent."Quota"))
This is what it should look like in the custom data field.
And now we have the quota percentage displayed below the progress bar!
5. Add formulas for conditional formatting
The last step before we start copy-pasting without abandon is to add the data for conditional formatting. I have two rules in mind: First, I would like to see at a glance if my quota percentage is 100% or higher. Second, I would only like the indicator triangle to appear under the current month.
We already have data we can use for the first rule (the quota percentage), but we’ll need to add a data field to the triangle. I’m going to call it “CM” for current month, and it will look up the current month column from the parent data and return a “Yes” or “No.”
The way we do this is—you guessed it—another if statement! The question we have this time is “Is it the current month?” and we would like the formula to return “Yes” if its true and “No” if its false. The if statement will look like this:
=IF(parent."Current Month" = parent."Month", "Yes", "No")
We did it! We can now start copy-pasting.
I made 12 copies of my shape and used the “distribute” function to make sure they are all lined up nicely.
Now, I can take the corresponding row and drop that data into the corresponding month, just like we did with the first row of data. It will ask you if you’d like to replace the data set, and you want to pick “Replace.” Then the shape will automatically update with the data from the correct month!
And now we have a nice visual display of quota attainment by month.
The last step (hooray!) is the conditional formatting rules.
