The daily research of costumer habits or imperfections is almost exclusively an analytics task. Detecting the different interrelations between customer and sales figures could increase real profit by

• stimulating particular product group sales
• increasing customers flow to the relevant centers
• etc.

In order to identify the key factors we should identify the correlation between each dimension. In Excel we have two types of such implementation:

The column chart

The dot chart

I learned about these two types of correlation graphs from Gene Zelazny’s book ‘Say it With Charts’.

The first way, how to graphically depict dependence between main keys looks adaptable. Accordingly, I’ll write how to make such a graph.

Let’s take this data sample and graphically represent the correlation between the number of customers and the number of sold items depending on the weekday:

In the column E insert the negative column C value:

The result should be this:

The data is prepared, so we can draw the first chart – select column D, press the chart wizard and choose BAR graphs:

In the X axis select column B (weekday).

Remove the sign from ‘Show Legend’ options.

In ‘Data Labels’ options note the data ‘Value’.

The graph:

Let’s make the second graph – select column E and draw the graph in the same way as first graph.

The result:

Both graphs are done, so let’s move on to the aesthetic presentation of the graph.

Polishing actions (the first graph):

• Delete gridlines and fill the background with white.
• Change X axis minimum value from 0 to 4000 and remove X axis – mark type and line ‘None’

• The Y axis ‘Major tick mark type’ should be ‘Inside’.
• Delete chart title and in the ‘Value (Y) axis’ insert the title ‘# sold items’

• Delete borders from bars.
• Fix the data label format

• First graph finished version

Polishing actions (the second graph):

• Delete gridlines and fill the background with white color.
• Change X axis maximum value from 0 to -2000 and remove X axis – mark type and line ‘None’.
• Edit the Y axis

• Delete the chart title and in the ‘Value (Y) axis’ insert title ‘# customers’.
• Delete borders from bars.
• Fix the data label format – it’s necessary because of negative values

• Second graph finished version

Combine both graphs into one:

• Just put the graphs side by side

• Edit the Y axis in graph ‘#sold items’ in this way – edit ‘Offset’ till 220

• Remove the chart area patterns.
• Select both charts and group them.
• The result

After successful schedule has been created the graph should be protected from accidental resizing. In this case we will use the command ‘Camera’ to do so. If you don’t have this button on your Excel toolbar you should add this command from ‘Add or Remove Buttons’.

The  ‘Camera’ Option

Select the created graph and press the ‘Camera’ button; after this, select another cell (free space). The result is like after Copy & Paste combination, but the main difference is that the user can change this graph size without damaging the original graph formatting.