What Is Venn Diagram In Excel?
Venn diagram in Excel helps pictorially represent the similarities and differences between logical datasets using overlapping circles. And we can use the SmartArt Graphic option to create Venn diagrams in Excel.
Users can use the Venn diagram in Excel to solve complex problems in Set theory, understand business processes and focus on addressable and target markets.
For example, the table below shows the total number of students with a score above 55 in a group of subjects. And the subject list includes individual subjects and combinations of subjects.
We can create a Venn diagram in Excel for the above dataset using the SmartArt Graphic feature in the Insert tab.
We use the Linear Venn diagram Excel template with four circles in the above example. And each circle represents the total number of students with scores above 55 in each subject.
On the other hand, the areas where the circles in the Linear Venn diagram Excel template overlap represent the total students with scores above 55 in the respective subject pair.
Table of contents
Key Takeaways
- A Venn diagramhelps understand the relationships between multiple datasets by comparing and contrasting their characteristics. And it typically displays the relationships in the form of overlapping circles. While the intersecting sections represent the similarities between the datasets, the non-overlapping sections represent the differences.
- Users can use Venn diagrams to present complex business processes and solve advanced probability and statistical problems.
- We can use the Relationship tab in the SmartArt Graphic option in the Insert tab to choose the required Venn template and insert it in a worksheet.
Top 4 Examples Of Venn Diagrams In Excel
Check out the following Venn diagram examples to use the feature in Excel effectively.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Example #1
We can create a Venn diagram in Excel using the Basic Venn template.
For example, the first table in the image below shows four number sets, P, Q, R, and S.
And the second table contains the information about the intersections of the sets.
We can make a Venn diagram for the above data, with the Basic Venn template containing four circles, as there are four sets in the source data.
Step 1: Select the SmartArt Graphic option in the Insert tab to access the Choose a SmartArt Graphic window. Otherwise, we can use Alt + N + M1 shortcut keys to open the Choose a SmartArt Graphic window.
We must select the Relationship tab from the menu on the left in the Choose a SmartArt Graphic window. And then, choose the Basic Venn template in the Relationship tab and click OK.
Clicking OK in the Choose a SmartArt Graphic window will insert the required Basic Venn diagram with three circles, as shown below.
Step 2: Right-click on a circle and choose Copy from the contextual menu or select the circle and press Ctrl + C to copy a circle.
And right-click again to select the Paste option in the contextual menu or press Ctrl + V to paste the chosen circle.
[Alternatively, we can right-click on a circle and choose the required option under Add Shape in the contextual menu.
The above step will add a new circle to the Venn template.]
Thus, now we have four circles, one for each set.
Step 3: Click inside the first circle to get the cursor for typing the required text inside the circle.
And after entering the required text, the set name P, use the Font group options in the Home tab to set the required text font size and make it bold.
Iterate the process to update the set names in the remaining circles.
Step 4: Right-click on the first circle and choose Format Shape from the contextual menu.
The Format Shape pane will open, where we can update the Fill option in the Fill & Line tab as Solid fill and set the required color for the chosen circle using the Color option.
Similarly, iterate the process by clicking the remaining circles one at a time and setting the required colors.
Step 5: Click the Insert tab and choose the Shapes option to select the Arrow shape.
And then, draw the arrow in the required position in the diagram.
Furthermore, we can select the arrow to enable the Format tab. And then, set the required arrow color and width using the Theme Colors and Weight options under the Shape Outline option in the Format tab.
Step 6: Choose the Insert tab and select the Text Box option under the Text option.
And then, draw the text box in the required position, as depicted below.
Next, enter the corresponding Excel Venn diagram overlapping section mathematical representation for the sets P and Q.
Next, we can copy and paste the arrow and text box in the template to display the information in each Excel Venn diagram overlapping section based on the given data.
While each circle represents a set, the overlapping sections between the circles represent the values common between the respective sets.
For instance, the intersection of the sets P, Q, and R, represented by the P ∩ Q ∩ R overlapping section in the diagram, includes the values {6,8,10}. It indicates that the values 6, 8, and 10 are common in the three sets P, Q, and R.
Example #2
We shall see how to draw a Venn diagram in Excel with data using the Linear Venn template.
The first table in the below image shows the different client accounts and the respective total count of engineers allocated.
And the second table contains information about the total number of engineers onboard for the projects of each client account, considering individual and different client combinations.
Here is how to use the Linear Venn template to build a Venn diagram in Excel with data based on the second table.
Step 1: Select the Insert tab and click the SmartArt Graphic option.
The Choose a SmartArt Graphic window opens.
Click the Relationship tab in the left menu and choose the Linear Venn template.
Clicking OK in the Choose a SmartArt Graphic window will display the following Venn diagram.
Step 2: Click the corners of the diagram border to enlarge it.
Step 3: We can click inside the circle to enter the required text in the circle, as explained in the previous section. Otherwise, we can click the arrow box on the diagram’s left border to open the window, which we can use to update the text in all the circles.
Click against each bullet to update the required text in each circle, one after the other.
And click the Close option to close the window.
Step 4: Click at the start of the text in the first circle, press Ctrl, and while left-clicking the mouse, double-click in each circle to select the text in all the circles.
Next, use the Font Size and Bold options from the Home tab to set the required font size and bold the texts.
Step 5: Click at the end of the text in the first circle and press Enter to get the cursor to the next line.
And then, enter the corresponding data based on the second table.
Iterate the process to update the required data in the remaining circles.
Step 6: Choose the Insert tab and click the Text option to select the Text Box option.
And draw the text box in the first overlapping area between the first and second circles.
And update the data based on the second table.
Step 7: Right-click the text box to choose Copy from the contextual menu or select the text box and press Ctrl + C to copy the text box.
And then, right-click again to choose the Paste option from the contextual menu or press Ctrl + V to paste the chosen text box.
Next, drag the copied text box and place it in the next intersection area between the second and third circles.
And update the data according to the second table.
Likewise, iterate the process to insert and update the text box in the third intersection area between the third and fourth circles.
Each circle in the Venn diagram shows the total number of engineers onboard for each client account. And the intersection areas between each circle pair show the total engineers onboard for the two corresponding client accounts the circles represent.
For example, out of the 100 engineers allocated for Apple and Microsoft, the total engineers onboard for each account are 50 and 80. However, 30 of them will work for both accounts simultaneously.
Example #3
Let us prepare a Venn diagram using the Stacked Venn template.
The table below shows a set of questionnaire statuses and the total number of students in each stage for getting accepted into an educational program.
Here is how to create a Stacked Venn template for the above data.
Step 1: Click the Insert tab and select the SmartArt Graphic option to open the Choose a SmartArt Graphic window.
Next, click the Relationship tab to choose the Stacked Venn template in the Choose a SmartArt Graphic window.
Clicking OK in the above window will insert the following diagram.
Step 2: Drag the diagram border from the corners to enlarge it.
Step 3: Click inside the first circle to enter the required text.
And iterate the process to update the remaining circles with the required text and data.
Step 4: Click at the start of the text in the outer-most circle and press Ctrl. And while left-clicking the mouse, drag the mouse on the text in each circle to select all the texts.
And then, use the Font Size and Bold options in the Home tab to set the required texts’ font size and make them bold.
Step 5: Right-click the first circle and choose the Size and Properties option from the contextual menu.
The Size and Properties tab in the Format Shape window opens.
Set the required Vertical alignment option under the Text Box option in the Size and Properties tab to view the text with the appropriate alignment inside the first circle.
Next, iterate the process to set the required text alignment in the remaining required circles.
Finally, the required Stacked Venn template for the data will appear as shown below.
The stacked circles show the total number of students in the program’s questionnaire update and acceptance stages.
The outer circle shows the total number of students who received the questionnaire in the first stage. On the other hand, the inner-most circle shows the total number of students finally accepted into the program.
Example #4
Let us see how to use the Radial Venn template to achieve the required diagram.
The table below lists the various customer sales promotion tools.
And the process to create the Radial Venn template for the above data is as follows:
Step 1: Choose the Insert tab and select the SmartArt Graphic option to access the Choose a SmartArt Graphic window.
Click the Relationship tab in the menu and select the Radial Venn template in the Choose a SmartArt Graphic window.
Click OK to view the below Radial Venn template.
Step 2: Drag the diagram border corners to enlarge it.
Step 3: Click the big inner circle to type the source data’s concept, based on which we aim to build the Radial Venn template.
Likewise, update the promotion tools in the four smaller outer circles, as depicted below.
Step 4: The text in the outer circle on the right side needs some adjustment. And for that, we can right-click the circle and choose the Size and Properties option from the contextual menu.
The Size and Properties tab in the Format Shape window opens.
Set the required Left margin to adjust the text inside the circle.
Finally, the Radial Venn template will appear as shown below.
The above Venn template shows the overlapping relationships and the relationship of the specified tools with the central idea of customer sales promotions.
Important Things To Note
- Use the Text Box option in the Home tab to display data in the overlapping sections in a Venn diagram.
- Click the inserted Venn template to enable the Design and Format tabs in the ribbon. And use them to change the Venn template, color, style, and format to suit the requirements.
- We can click inside the circle once and enter the required text. Otherwise, we can click the arrow icon on the left border of the diagram to enter the texts in all the circles in bullets, one after the other.
Frequently Asked Questions
We can make a proportional Venn diagram in Excel by varying the sizes of the circles and the intersecting sections according to the relationship they represent.
For example, we have two sets, M and N. While the value in set M is 150, set N contains 100. And let us consider the total value is 200.
Then, the set M intersection set N will be 50, as explained in the third table in the image below.
So, here is how to represent the above data with a Linear Venn template.
Step 1: Choose the Insert tab – SmartArt Graphic option to access the Choose a SmartArt Graphic window.
Choose Relationship – Linear Venn.
And click OK to insert the following Linear Venn template.
Step 2: We require only two circles in the diagram as we have two sets. So, we will select the last circle and press Delete to remove it.
Likewise, we shall select the last circle and press Delete to remove it.
Step 3: Click inside the first circle to enter the first set name M.
Next, press Enter and type the set value, 150.
Likewise, update the second set name and value in the second circle.
Step 4: We must resize the circle to create a proportional Venn template, as the set M value is larger than the set N value.
So, we can reduce the second circle’s size. And for that, right-click the second circle and choose the Size and Properties option from the contextual menu.
The Size and Properties tab in the Format Shape window opens.
Step 5: Set the required Height and Weight settings under Size in the Size and Properties tab to reduce the second circle size.
Step 6: Choose Home – Text – Text Box.
We can now draw a text box in the overlapping section between the two circles.
Step 7: Update the set M intersection set N value, 50, in the text box. And set the required font size for the value in the text box and make the value bold using the Font Size and Bold options in the Home tab.
Finally, the proportional Venn template will appear as depicted below.
We can do a Venn diagram in Google Sheets using the following steps:
1. Open a Google Sheet.
2. Choose the Insert tab – Drawing option to open the Drawing window.
3. Select the Shape option – Shapes option – Oval shape.
4. When you hover over the work area in the Drawing window, the cursor will appear as a Plus symbol. You can now draw the first circle and iterate the step to create the required number of overlapping circles to represent the given sets and build a Venn template.
5. Click one circle at a time to type in the text. And iterate the process to update the required texts in the remaining circles.
6. Use the Text box option to insert text boxes and update data in the required overlapping sections in the diagram.
7. Click Save and close to save the Venn template in the Google sheet.
We can make a 3 circle Venn diagram in Excel using the Basic Venn template since it contains three circles by default.
Otherwise, we can insert a Linear Venn, Stacked Venn, or Radial Venn template. And then, select one circle at a time in the diagram and press Delete to remove the extra circles and achieve a 3 circle Venn template in Excel.
Download Template
This article must be helpful to understand the Venn Diagram, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Venn Diagram. Here we learn how to make Venn diagram along with step by step excel examples & downloadable template. You can learn more from the following articles –
Leave a Reply