Maslow’s hierarchy of needs is a theory in psychology proposed by Abraham Maslow in his 1943 paper “A Theory of Human Motivation” in Psychological Review. You can read more about it here. You have most likely have seen many depictions of his theory as a pyramid of “needs”. A typical one looks like this:
In this blog, I share how to create a similar chart based on underlying data in Excel. That can be used for many similar applications. The same steps can be applied to create other shapes in Excel. We’ll also look at how to base labels on the data set. As a bonus, we’ll add a drop-down list with data validation and lookup to augment the chart information.
The goal is to create a chart as below in Excel such that values can be easily changed in the data for other applications without having to re-create the graphics manually each time.
The idea is to have a pyramid chart with smallest value at top going downward toward the largest value with a smooth incline. Each layer will be the “need”, and its dimension will be dependent on the value assigned to it. On one side, the “need” labels will appears at each layer (stratus), and on another side we’ll add a general category label encapsulating the need(s) >=1 as shown above.
First, I created a data set with the following values and labels:
|Food. Water. Air. Shelter. Warmth. Rest.
|Security. Safety. Order.
|Intimacy. Friends. Trust.
|Dignity. Prestige. Achievement. Status.
|Creative activities. Growth. Curiousity. Aesthetics. Mystics. Exploration.
For the chart’s initial form, we’ll only need the Need and Value column. So, choose those 2 cols and Insert a 3D Stacked Column chart. If you get something like this as a default:
select it and you then edit the chart and Switch Row/Column…in which case you’ll get the boxy stacked column as below:
This may look far from what we want, but we’re very close! Right-click on a column bar slice, and format data series->Full pyramid.
Now it’s beginning to look like our final chart. But a few more things we need to take care of first.
From Chart Design menu, click Change Colors button, choose Monochromatic colors. I chose the green theme light to dark. Also, remove the axes…we don’t need them. It now looks like this:
We will however need labels…several of them. But the chart won’t help us directly there. We will instead Insert a Text box from Insert menu. While it’s selected, click on the formula bar and enter the name of the slice from data (e.g. =B3. See the table above? I have Need values in column B and row 3 is the bottom-most layer).
The label’s text turns into B3’s value, which is “Physiological”. Now we have to align this to the bottom layer. You can rotate it or click on the text element to Format Shape, then on Effects in Format Shape pane: under 3-D Rotation: set Z Rotation to match the pyramid’s angle (e.g. 355 clockwise).
To save time and my wrist, I copied this control and pasted and modified each one’s formula for its label. So, the next one’s formula is =B4, and then the next one’s is =B5…until B7.
Then we need to position each on the chart on their own slices. Note that you’ll have to change the angle of rotation is because it’s a pyramid…angle is changing by the layer.
After each label is positioned, it’s a good idea to group them (or you can do it later) to move them around easier with the chart as needed.
NOTE: You may need to change the width of pyramid from Format Data series: Gap Width (e.g. 10%) to make room for labels.
Insert->Illustrations: Shapes: Line. For the needs general category labels along the right edge of pyramid.
From + add chart elements, uncheck Gridlines. Now, your chart should look like this (of course, you need to play with the font, size, line types/colors as you desire):
You can modify it further to suite your taste/needs but I’ll stop here with the chart. I am done! The only thing I want to add is a drop-down list for Needs. So, when I want to know what Esteem means according to Maslow, I can retrieve that information straight from my table. This info is in column D for each Need value in row.
In an empty cell, add a list control via Data->Data validation menu. Choose List, and source (in this example) B3:B7. Next to that cell, will be the results of the choice shown based on selection in the drop-down list. So, let’s add a lookup formula to get that from our table =VLOOKUP(F89,B3:D7,3,FALSE)
NOTE: I did not technically use a table for this example, just a range. You could use a table and reference it by its name instead (or name the range, and call it by that name). Also, I happen to put the control in F89 but you should a different available cell closer to your table or in a separate sheet altogether. For details on vlookup(), you can see Excel’s online help (also, I have extensive examples of that, index-match, xlookup and more available separately).
See it in Action!
This is a live Excel workbook that’s embedded. Click on the green cell and a down-arrow will appear next to it (see image), click it to open the list of items to choose from. These are the “needs”…choose any to get its brief description that appears next to it in a light blue cell.
Now, that’s a beautiful chart, don’t you think? 🙂
If you like more info or actual source file to learn/use, contact me via this form or directly by emailing me at trseattle at outlook dot com. To support this voluntary work, you can also donate via the Home page.