Get best of the week

Infographics using Excel and PowerPoint. Part 2

Do not wait for a trip to Gagra!
David Markovich, the series "Liquidation"

Hello everyone!

During the quarantine period, probably, not once did everyone read about how fruitful A. S. Pushkin spent his “imprisonment” period, later called the “Boldinsky Autumn,” so I won’t explain what exactly prompted me to write the next article on infographics using MS Office tools.

In addition, I have something else to tell you, because for more than 10 years I have been teaching and leading a group for the development of teaching materials at the LANIT Network Academy training center , and I am also the author of the course on infographics.

In the first articleI gave some examples of using icons in charts instead of a legend and for visualizing the final value. I propose to continue the topic for watching movies. Over the past couple of weeks I have watched more movies than in a whole year, and this prompted me to combine the examples in question into groups and choose the name of the film for each group with which I associate it. 


I'm legend


As you may have already guessed, we will talk about a legend ... for diagrams.


There are several options for replacing the legend with icons. We considered one in the previous article , in this - we will consider two more. The first is replacing chart markers with icons, and the second is filling rows with icons.


Let's start with the first one. He is the simplest.


, . – , . , , , .

. … 

1500000, 2000000 — . 100000 , , .

. …

().

: . , , .

Webdings . , Office365, . , . , . 

, : , . CTRL+V.

. 

With the first option we are done. I propose to immediately proceed to the second.


To build the next option, we need to add cells with values ​​of 100% for each of the two rows in the existing table. Then, select the range B1: C3  and construct a bar histogram with grouping.

Using the Chart Elements button , turn off the axes, chart name, grid, legend, but add data labels. Then select the row signatures 100% and delete them with the Delete key.

For any series with data in the context menu, select the command Format data series ...

In the Format of data series area, on the Series parameters tab, for overlapping rows, set the value to 100%, and for the side gap 50%.

…

, , . .

. Impact 24 .

Webdings. ? Excel. ,   .

, / . 

: , . , 100% CTRL+V.

…

. , .

: , . , 72% CTRL+V.

.

, .

, , 100% 10 , , .

So, now in your archive there are three different use cases in icon diagrams instead of a legend. 

For those who want more, I propose to try to build this version of the diagram yourself.


You can create your own data, and the symbol with the image of a stack of books from the Webdings font will help you! 

Lord of the Rings


My precious…


Already about Excel in pie and donut diagrams only lazy does not know. They are simple and visual. They really like to use them for data visualization.  

We also already considered one option for constructing a donut diagram in a previous article , in this I suggest analyzing two more examples.

The first is for visualizing one final value, and the second for several.


By tradition, we start from the first.


, . 100% . .

, .

… 

70% . , , .

, ,   …

.

, , .

.

, 12 . , .

. , . , , .

, , , . Enter.

, . , , Impact 28 .

Try changing the final value, and you will see how beautifully your little ring is redrawn.

We consider this example as part of the Infographics course using Excel and PowerPoint , but we complicate it a bit by adding a condition under which the color of the ring is red below the first defined value and green above the second specified value. And only if the value falls into the interval, the ring will be orange. 

And now for the next example.


We have a table with two data. We will do additional calculations and in the next column we calculate the difference between 110% and the values. I added 10% to 100% in case the values ​​themselves are more than 90%, then the ring will not seem visually closed. Next, select the data range with line captions and build a donut chart.

, .

… 

50% 180 . , , .

, , … : , , .

.

, , – , – . 

, , 3 , 1 . , .

. , . , . , , , . , 4 .


Webding . , .

.

, , , . Enter.

/. , , 12 .

.

, . Enter. , , .

We will draw a circle using the Shapes command of the Insert tab , but don’t forget about the SHIFT key. Place it and adjust the dimensions so that it fits into the center of our diagram.

Using the commands on the Drawing Tools / Format tab, we will assign a gray fill and effect to our Shape 1 . 

Select the diagrams and all additional objects and group them by selecting the Group / Group command in the context menu or on the Work with Drawings / Format tab .

Well, I’ll repeat the diagram and suggest that you make another version of the donut chart yourself.


In this example, the values ​​are not in percent, but the maximum value is 1000, so additional values ​​should be calculated relative to it. Good luck

mission Impossible


Feasible or impossible, it's not for us to decide, but it’s very interesting to watch the process ...


Very often, charts are used to display the result of the current process, that is, in order to understand how much is still before the finish.

Here are a couple of examples. Such charts are also called progress charts, and the first option is often called a thermometer.


Let's start with the option of constructing a diagram in the form of a thermometer. We will need additional data dividing the construction area into three sections.


1, A3:D5 . 60%, 30% 10% . 3 1, 2% 4, . 5 =100%-3-4

B3:D5 . , , .

, - . 25%. 

, .

Now for the middle column, we assign a special formatting. For the darkest rectangle we assign black color, for the central rectangle we assign red color, and for the lightest rectangle lighter gray. 

In the context menu of any series, select the command Format data series ...

In the Format area of ​​the data series, set the lateral clearance 0%.

Reduce the width of the chart.

Now select the data point, that is, a black rectangle, and using the Chart Elements button , configure the display of the data label inside at the edge.

Select the data signature and assign a bold face and a white font color.

And we will build the second example of the diagram with a little trick. Here we need the PowerPoint functionality.


2 , , D2 100% 2.

C2:D2 . , \ \. 

, , . .

…

0%.

- . 

PowerPoint Webding. , 8 10 . . 300 . , .

CTRL+A, / .

, Excel , .

, /.

2 100%. . , , . , .

, , .

C2 .

Select a diagram, a drawing and an inscription and group them using the Group \ Group command in the context menu.

Are you already waiting for an example for independent implementation? There he is.


Illusion of deception


The more it seems to you that you see, the easier it is to deceive you ...


In no case am I going to deceive anyone now, but there will still be an element of focus or illusion. 

There is a command in Excel called Camera , it must be forcibly added to the tape. This is such an uncommon command that I had to stumble upon its use only once in the 2013 version of Excel in some overview materials. It was not love at first sight, I did not immediately see its potential. I began to make friends with her only after three years, when on the Internet I came across a demonstration of creating a dashboard of indicators of a process using this particular command.

Its task is to take a picture from a range on an Excel sheet and place it on another sheet of this book. Moreover, the range is inserted as a picture, but a picture that will be updated if any changes were made in the original range or data range.

Figuratively speaking, it turns out a certain window from one sheet to another.

This command will allow us to create entire dashboards without resorting to specialized add-ons. 

Here is an example of simply placing snapshots of diagrams on one sheet.  


At first glance, these are simply placed diagrams on a separate sheet, but this is the illusion. These are snapshots of cell ranges that display diagrams placed on a separate sheet. Since these images are no longer ranges and not diagrams, but images, the formatting parameters are similar to those of the figures. See how you can convert these pictures using the Draw / Format tab .


Imagine, we can use cropping, effects, and drawing styles for these objects, but the connection with the data is preserved. Here you have all the cards in hand, but do not overdo it.

If you are ready to take a closer look at this team, then let's start with where to find it and how to add it to the tape.


.

Excel . : .

. , , .

. , .

Make sure that the Camera command is highlighted in one field , and the New group (customizable) in the other field , and click the Add button . The Camera command will be added to the new group.

If desired, you can rename the group, and click OK to complete .

Now on the Home tab there is a button


It's time to figure out how it works.

We will look at a single diagram example. We’ll take a snapshot of this diagram, place it on a new blank sheet and make a number of formatting settings. As a result, we get such an image.


Let's get started.


, . , . , . , . – .  

.

. .

, , . , , .

, - .

, .

, /   .

.

And in order to finally convince you of the usefulness and magic of this team, I propose changing the initial data on the sheet with the chart, and then go to the sheet with the picture and check the result.

On the chart sheet, enter 75% in cell B1 and immediately see that the chart is redrawn. Well, there’s nothing special, we’re already used to it.


Now go to the sheet with the picture ...


The image is updated at the moment of transition to the sheet, and you can see the process of redrawing.

I hope you do not hide this knowledge and skills in a distant box, but will apply them. Personally, I believe that the scope here is huge.

Tell us in the comments which example was most interesting to you. Was all the functionality familiar and used by you? Share what examples of diagrams or data visualization you would like to see in future articles.

And finally, a pleasant surprise for the most attentive and patient readers - a 50% discount on the online course on infographics for the INFOHABR coupon. The coupon is valid until August 31, 2020, and access is open for 3 months. Only shhhh!

See you online or at our courses!

More articles:


All Articles