In this session, we’re going to look at how to select data, and we’re going to make column charts. As an example: specifically clustered column charts as we’ve seen, Alt f1 will quickly make column charts. If I just delete that, there are other ways to create a chart with this area selected. Using Control A, we can go to insert, and then we can go to recommended charts. Excel gives us a choice of the charts that he thinks we most likely want to use. We could also go to all charts and see all the available charts.
As I said, this is a clustered column, and it’s ideal if we’ve got two or more rows of data and two or more columns of data. Add some more data here. If I want to incorporate this additional data in a chart, I would just set the first range of data and then hold the Ctrl key down to select the second range of data. Again Alt f1, and this time we get a clustered column chart. This context clustered means that the categories are grouped.
Edit the Chart Range
For this, we’ve got the two years grouped; we’ve got two years to group together, so come out there and delete that chart selecting this data to the left again. If I want to edit the chart range, I have a couple of options. First, I could literally just highlight this data with Control C.
Click over the chart Control V and the data is added to the chart. Undo that, another option to right click and go select data. If the data was contiguous, I could go to a short data range and just edit that from C to D, but it’s not. So, I need to go down to legend entries and add series names and series values.
Firstly, hit the Delete key to eliminate the series values and then highlight the data that we want to add. We achieve the same thing, which is exactly the same: going to make column charts’ design and selecting data brings up the same dialog box. So, cancel out there just by adding this data for cells 18 back with Ctrl C and Ctrl V. Again, this chart is great when we’ve got two or more rows of data and two or more columns of data.
Use Direct Comparisons between Comparable Data
We want to make direct comparisons between comparable data. So, for example, we can see how these cells have compared from one year to another to make this chart bigger. If we want to change the look and feel of the chart, let’s just go to chart design. Firstly, one option we have is a quick layout here; we can see different configurations of the same chart so, if I hover over them, we’ll see.
For example, this one brings in data labels and a legend. This one brings in a data table below the chart, and this one overlays one of the bars partly over the other bar.
Go To Chart Style
Next, I can go to make column charts’ styles. We have quite a range here. So, I can really sort of change the look and feel of the style of the chart. We’ve also changed the chart type up in the ribbon if we want to refine the chart type. We have various types of columns.
We could perhaps go for a stacked column instead of a clustered column for this particular data set. This is probably a little bit harder to read, but it’s worth knowing about, and we also have more charts. If we want to relocate this chart when we make column charts, set the chart up.
If we want to make some quick and easy changes to the chart, go to this green plus to get the chart elements.
For example: Maybe, we would like a data table. Then we can see all the data for the two years immediately below the chart. It’s quite useful to see the data visually in a place adjacent to or otherwise very close to the graphical representation. Sometimes, data labels can be quite crowded, and you may need to play around with the settings.
Experiment the Variations
The font’s color of the label contrasts and the color of the bar’s background on which it’s sitting on top or you may want to relocate the labels. We also have a legend, so; we can see the legend here. Legends are quite helpful. Sometimes the legend can be a little bit too far away from the graphic that it’s related, so, visually, the eye has to track the legend and then track back to the graphic.
And of course, if we don’t want to try all of these customizations, we can just go to a quick layout and experiment with all kinds of variations.
How to Use Format Alignment
In the home tab of the ribbon, we have a section dedicated to alignment. Firstly, if I select some cells and click on some of these items; for example: With a line left, we can see all the numbers move over to the left, and then if we align right, we can see everything get aligned to the right, and we have a line center as well.
Increase the row height on these rows to demonstrate another option up here. We can align everything to the top, the center, or the bottom of rows; also, I would use wrap text with an alignment quite often.
How To Use Format Alignment Use Merge Cell Option
If I type something into a cell and click return, you will see that the contents of this cell overlapping all the cells. If I start typing something in the next cell, we will lose visibility of the contents. So, if we use wrap text, we will then wrap the text to see several lines in a cell. Excel is not optimizing the cell width or height, but it’s giving you the potential to see more in a cell. This way, if you want to be able to see everything in the cell, you can.
Either auto height or auto width, and the final option over here is merge and center. I have typed some text in cell B1. If I then highlight B1 to G1, we have merged and centered options. The first one is merged and centered. So, as we can see, this centers the text across the selected cells. If I undo this alternatively, we have merged across.
But we’re aligned to the left, or we can just merge the cells. We can unmerge them like this. Personally, I try to avoid using merge as your spreadsheets grow and become more complicated, and you use functions such as sorting as well as moving and copying. Merge often causes problems; an alternative center across a selection doesn’t have the drawbacks of merge yet achieves the same visual effect to access center across selection using the down arrow or Ctrl 1.
Explore Format Cells
Click on the arrow, and if we go to alignment, we can choose the center across the selection. So, here we’ve centered the text across the selection of cells but haven’t actually merged them. So, we have perhaps achieved the same presentational objective, and we’ll find it later on. As we get more advanced, we’re less likely to have errors or issues with this approach. Thank you!
