Your world in MS office

Monday, March 15, 2021

Bubble chart in Excel

Bubble chart in Excel:

Bubble chart show the relationships between sets of values.

Bubble chart compare at least three sets of values or pairs of data.

You can use it when you have a third value that can be used to determine the relative size of the bubble.

Process of creating a bubble chart in Excel:

Select your data range B3:D6 to create bubble chart.

Go to insert tab ➤ charts group ➤ select insert scatter or bubble chart ➤ select 3D Bubble chart.

Now 3D Bubble chart immediately appears on your excel sheet.

Click on chart title to edit it.
Click on any bubble on your chart ➤ right click on a bubble➤ click on format data series.

A format data series panel appears ➤click on the paint bucket tool ➤ select vary colors by point.

Now your chart looks like the below.

Here you can change the chart style from design tab
Go to design tab select any chat style from chart style group.

Result:

To add data labels click on the + icon on the right side of chart ➤click on data label ➤go to more option.

Now Format data labels panel appear.
Here you can select bubble size, show leader lines which you want to add in your chart.
Also you can add label position center, left, right, above or below of your bubble.

Now the result shows like the below.....

To add legend click on + icon on the right side of your chart ➤ click on legend.

Result appears showing legend like this....

To change the shape of data labels right click on the data label of your chart ➤ change data label shapes➤ select any shape

Now data labels appears with your selected shape. Slightly move them to fix to correct position.

In this process you can create a bubble chart in excel.

Monday, March 8, 2021

Scatter chart in Excel

Scatter chart

Scatter chart is used to show the relationship between sets of values.

Select the range of data B2:C18

Go to insert tab click on scatter chart.
Select scatter.

Now your scatter chart appears immediately.

Here you can add a trendline to see the relationship between the two variables(square meter and prices of apartment)
Click on the + icon on the right side of your chart➤ click on trendline option➤ select linear.
Now your trendline appears like this.

How to create scatter with straight line:

Use this chart type to compare at least two sets of values or pairs of data.

When there are many data points you can use scatter with straight lines. The data represents separate measurements.

To create a scatter with straight lines chart select your data range A1:B10.

Go to insert tab➤ charts group➤ click on scatter chart➤ select scatter with straight line.

Now your scatter chart with straight line appears. (here I added a chart style from design tab to see the chart clearly)

You can add chart title here.
Double click on the chart title and rename the chart title.
To add data labels to your chart click on the + icon on the right side of your chart ➤ click on data label ➤ select where you want your data labels in your chart( center / left/ right/above/below)

Now your chart looks like this👇

In this process we can create a scatter chart in excel.

Pie chart

Line chart

Friday, February 19, 2021

Pie Chart

Pie Chart:

Pie Chart is used to show proportions of a whole. Pie chart is used when the total of your numbers is 100%.

To create a Pie Chart select your data in Excel worksheet.

Go to insert tab ➤ charts group ➤ click on Pie Chart.

Select any 2D or 3D Pie

Excel creates a pie chart for your data.

Click on +icon on the right side of the chart to add chart elements (chart titles, data labels, legend)
Click on chart title ➤ select above chart/ centered overlay.

Click on + icon➤ click on Data Labels ➤select any one of them(center/inside end/outside end/best fit/data callout)

Click on + icon➤ click on legend➤ select any one of them( right/top/ left/bottom)

Click on the brush icon and select any style and color for your chart.

Click on the funnel icon to add chart filters.

Right click on the pie chart and click on format data labels.

Format data labels➤ check on category name, percentage and uncheck the value and others.

Now your chart looks like the below.

line chart

column chart

Wednesday, February 17, 2021

Line Chart / Area chart

Line Chart / Area chart

Line chart or Area chart are used to show trends over time or categories.

Select the range of data that you want on chat.

Go to insert tab ➤ click on line chart from charts group.

Insert any 2D or 3D line chart ( in this example I choose Stacked line chart)

Now excel immediately creates a chart and place it on your worksheet.

Right click on the line to change the line color.
Click on Format data series.

A pane named Format data series appears.
Click on fill & line (pain bucket icon) to change the line color.
Click on solid line/ gradient line/ automatic (here I selected solid line)
Click on outline color ➤ select any color.

Click on marker to change the marker color.
Click on marker options ➤ Built in➤ select marker type and size➤ click on Fill and select fill color ➤ click on border and select border color.

Now your chart line looks like the below

For more format option click below 👇

chart elements, chart filters, chart styles

How to move and resize your chart

Column Chart

Monday, February 15, 2021

Moving and resizing a chart / changing the chart type / changing the layout of chart/Insert Sparkline

Moving and resizing a chart / changing the chart type / changing the layout of chart/Insert Sparkline

Moving and resizing a chart:

You can move and resize an embedded chart on your worksheet.

Move a chart:

Click on an empty area of the chart. Excel selects the chart and surrounds it with handles.

Move mouse over the edge of the chart. Here the mouse pointer changes.
Click and drag the chart to a new location on the worksheet.
Release the mouse button. Now your chart moves.

Resize a chart:

Click on an empty area of the chart. Excel selects the chart and surrounds it with handle's.

Move mouse over the handle of the chart. The mouse pointer changes ( a dimmed gray border represents the chart as you resize it on the worksheet)
Click and drag a handle to resize the chart.

Release the chart.
Now excel resizes the chart.

Changing the type of chart:

You can change the chart type to present your data more effectively after creating a chart.

Click on an empty area of the chart.
Click on design tab on the ribbon.

Click on change chart type button.

Now the change chart type dialog box appears.
Select the chart type which you want to choose.
Click on the chart type ➤ select your chart style ➤ click on Ok.

Now excel changes the chart to the chat type that you selected.

Changing the layout of chart:

You can change the chart layout to change how chart elements are positioned.

Click on an empty area of the chart.
Click on design tab on the ribbon.

Click on quick layout from the chart layouts group.

Click on the down arrow button to see the full palette of layouts.
Select your layout and click on the layout.

Now excel applies new layout to the existing chart.

Add chart element:

You can add X and Y- axis titles on your chart to identify your chart data. X-axis displays the horizontal value and Y-axis display the vertical value in a chart. Under add chart element you add axes, axis titles, chart titles, data labels, gridlines, legend, lines.

Click on add chart element from chart layouts group.

Here you add axes, axis titles, chart titles, data labels, gridlines, legend, lines.....

Add Sparkline:

Data analysis is quicker and more direct to the point with the use of Sparkline.

In excel Sparklines are tiny, word sized charts that can appear in a single cell, each representing a row of data in your selection. It is very easy to create Sparkline.
To create a Sparkline click on the cell where you want the sparklines to appear ➤ go to insert tab➤ sparklines group ➤ click on column sparkline.

Create sparklines dialog box will appear.

Now select the range of cells that will be the source of the data for the chart.
Click on Ok.

Now Sparkline appears in the selected cell.

Now the design tool appear on the sparkline tools tab. You can edit and format the sparkline by using these tools.

Column chart