How to Make Graph in Excel

17
18631
Make more attractive graphics with 10 simple tips
Excel Spreadsheets

One of Excel's best analysis tools is graphics. With them you get insights and full understanding of what's happening with your spreadsheet information in a matter of seconds. The only problem is that not everyone who uses Excel knows how to make charts that are really explanatory and easy to analyze.

Advanced Charting Course in Excel

To start with, let's learn how to assemble a chart

The process is quite simple and can be done in several different ways. The easiest and quickest is when you select the data you entered in your spreadsheet and choose the option of adding a spreadsheet. In our example below, we selected the range of A2 cells: B7, which comprises the toys and their quantity. After making the selection, simply go to the "insert" tab and select the "add new table" option:

Make more attractive graphics with 10 simple tips

This first process has nothing to do with graphics objectively, but you'll see later that this type of action can and will make it more practical to manipulate your spreadsheet information. After the table is created, again on the "Insert" tab, choose the type of chart you want to create. In this post we will work with the column model:

Make more attractive graphics with 10 simple tips

With these basic steps, the chart is already created, but with these 10 simple tips, it will look even better.

Advanced Charting Course in Excel

10 Tips to make your graphics more attractive

1) Eliminate visual pollution

Excel's standardized graphics maintain lines to mark the axes, supposedly making it easier to understand the content. In most cases, however, these lines tend to divert the focus from who reads the information. Prefer graphics that are unpolluted. To remove the baselines from your chart, simply click on them with your mouse and press delete. Here's how it looks:

Make more attractive graphics with 10 simple tips

2) Use a really explanatory title

When only the chart is in evidence, a clear and objective headline informing which data is being analyzed is very useful and prevents the less focused audience from getting lost during the presentation. Instead of just "Results", for example, it is much better to use a heading "X sector results x in the second half of 2014". To change the title of your chart, simply select the title given by Excel (which is the same one used at the top of your table) and insert a new one.

Make more attractive graphics with 10 simple tips

3) Do not use captions that identify a single element

If the graph shows only one die, instead of describing it in the legend, present the information directly in the title. To delete it, just select it with the mouse and press delete:

Make more attractive graphics with 10 simple tips

4) Change the location caption

This tip is for when the graphic has more than one item in the legend, and especially for pizza or line models (with more than one line). Anyway, the place where you will leave the legend will depend much more on the size of the graphic and layout of the content than on anything else. For example, a longer graphic may usually have the legend on the right side, a longer one, you can put them underneath, and so on.

Advanced Charting Course in Excel

5) Keep text horizontal

It is rather unpleasant having to tilt your head to read the information on the chart. If the information does not fit all horizontally, use bar graphs instead of columns and make it easier for anyone to analyze it! In our example, it's worth increasing the size of the chart or choosing the bar model:

Make more attractive graphics with 10 simple tips

6) Sort the data before plotting

If the graph shows data that is sequenced, as in the case of those related to time progression, this tip is not applicable. However, if the order of information in the chart does not matter, it is much better that they are organized in ascending or descending order of value. Reading these numbers gets much simpler and the variations more noticeable. The ordering must be done in the table that you used to construct the graph, and it will automatically look cute like this:

Make more attractive graphics with 10 simple tips

7) Delete unnecessary decimal places and use the "thousands separator"

Numbers with many zeros confuse readers less attentive, so the ideal is to avoid putting values ​​like R $ 10,00. It is much simpler to use only $ 10. Also, it is interesting to use separators in numbers that have the thousands (as in 1.000.000) for easy reading. To make this change, simply click on the corresponding axis with the right button and open the axis formatting box. In the "number" option, just enter the formatting code shown below:

Make more attractive graphics with 10 simple tips

Advanced Charting Course in Excel

8) Adjust the chart scale according to the details to be viewed

To make a bar chart lighter, it is important that the information is not too close, with too many values ​​on the axis. To modify the main scale unit, select the graphic and open the format box (Ctrl + 1). Then select the axis containing the values ​​with the mouse, change the main unit from "automatic" to "fixed" in the axis options and enter a value greater than that used by Excel. If your chart requires a greater amount of detail, set its dimensions so that all values ​​are properly spaced and readable. Changing the value of our chart from 2500 to 5000 results in this:

Make more attractive graphics with 10 simple tips

9) Use Data Labels

Whenever possible, choose to have the values ​​for each column above it, so it is easier to understand the actual value to which each of these columns corresponds.

Advanced Charting Course in Excel

10) Change themes!

The theme preconfigured by Office is the most used, mainly by those who do not know the most of 2500 combinations offered by Excel. You can make various combinations of colors and styles and find one that fits the presentation proposal, including customizing the colors of your brand, by clicking on some part of the graphic (the bars, for example) and accessing the formatting. Here's an interesting example:

Make more attractive graphics with 10 simple tips

Only be careful not to end up with a graph similar to this one:

Make more attractive graphics with 10 simple tips

By following these simple 10 tips your work will for sure be much more readable, and the graphics will fulfill its function: facilitate understanding of the content presented.

Advanced Charting Course in Excel

Excel Spreadsheets

17 COMMENTS

  1. Certainly Paulo. Besides being an important tool, it helps a lot in decision making

  2. It is very useful these days to know how to make graphs of all kinds in Excel because Excel is an important tool worldwide.

  3. Isa may be that your question is over, but would it work for what you want?

    =SE(B49<=8;"";SE(B49<=15;"Definitivamente matutino";SE(B49<=20;"Moderadamente matutino";SE(B49<=26;" intermediário";SE(B49<=31;"Moderadamente vespertino";SE(B49<=38;"Definitivamente vespertino";""))))))

  4. Hi Joice, how are you?

    In this case I imagine that you will need to have a table with the dates and probably 1 and 0 values ​​to confirm the presence. If present = 1 and if missing = 0. That way, when creating the chart, you will have the variation happening according to absences and attendances ok?

  5. Hi Rafael! I love dealing with spreadsheets in excel, and I'm really in need of help creating a spreadsheet, specifically. I need to create a spreadsheet showing the frequency of students attending Sunday school for the month. For example: Student "A" was in Sunday school on 03 / 04 and 10 / 04 days, and was missing, on days 17 / 04 and 24 / 04. Then the graph of this student should have a rise, which he attended on 03 / 04 and 10 / 04 days, and should have a fall due to lack of 17 / 04 and 24 / 04. I already created the spreadsheet with the data, and I came close to getting the chart I need, but I did not succeed. I really need this help. If you can, I thank you very much !!! Joice.

  6. Perhaps the problem is that after writing the formula she appeared written with a few different characters that left her "wrong"

    In the above formula where you have "" & or it you need to delete. I will try to write again in a new response, but it may be that the formatting of the comments manager makes changes again

  7. I really do not understand how to make this formula, could you answer me by email? Maybe I did not give this formatting error that is giving here in the forum .. I'm sorry for continuing to bother about it, but I do not know anyone who can help me

  8. Hi Rafael, good morning.

    I already tried your excel steps and it worked. Thank you so much for your help.

    Thank you.

  9. Hi José, I'll answer you right here, okay?

    Basically you can follow this step by step:

    1 - select the first and second line (from the question column until the column fully agree)
    2 - go to the insert tab
    3 - choose the recommended graphics option
    4 - Insert a pie chart

    So you will have the distribution for the 1 question. After that, you can copy and paste the chart and just go changing the references to have the distribution of each question in charts.

  10. Hello Isa and Rafael I'm having a problem building a chart.

    I have the following table below (append) and thank you for telling me what steps I have to do to create a chart and what tb what kind of chart should I create.

    The table has 95 items, consisting of Issue Number, Related Item, and Categories.

    Thank you very much for your help as soon as possible.

    I would like you to send me an email: [Email protected]

    I await your reply as soon as possible

    José Moreira

  11. Hi Isa, I understand the logic you used, but the SE function and Excel do not allow you to use two logic in this way 16 <b49 <20 and so on. To use two logics you can use the function E. It would look something like:
    .
    =SE(B4916;B4921;b4927;B4932;b49<=38;"definitivamente vespertino")))))

    In the function that you put here, besides this error, I also noticed a wrong quote in the wrong place and the function ending with the big one, which would not be correct. I imagine it was a formatting error here from the Forum that is also appearing in my reply.

  12. Good afternoon, I had a problem in my questionnaire, in the end I need to tabulate the type values: if from 9 to 15 points, it has to show "definitely morning" result if it gives from 16 to 20 the result is "moderately morning" are 5 answers possible tried to do with the formula if, but it did not, can you tell me the error?

    =SE(B49<=15;"Definitivamente matutino";SE(16<b49<=20;"moderadamente matutino";se(21<b49<="26;"Intermediário";SE(27<B49<=31;"Moderadamente" vespertino";se(32<b49

LEAVE AN ANSWER

Please, write your comment
Please enter your name