Excel Charting Archives

How To Create An In Cell Bar Chart Using The REPT Function In Excel -Formula Friday

July 8, 2022 by Barbara

Hello Excellers it is Friday again and time for #formulafriday where we have some Excel formula fun. Have you used the REPT function in Excel or ever used it?. It’s a bit elusive so let’s get it out of the shadows and bring into the limelight as see how we can use it!. Are you ever short of space on your Excel worksheet? Did you know that you can create in-cell bar charts?. You can do so using the REPT function in Excel and produce a very handy Excel chart like the one below. They are really easy to set up and very simple to use.

Did You Know About The REPT Function?

In my experience, not that many people have heard of or use the REPT function. So what does it do?. The Microsoft Excel REPT function returns a repeated text value a specified number of times. The syntax of it is as follows-

=REPT(text, number_times)

With the following arguments.

text – this is a required argument, and is the text you wish to repeat.

number_times – this argument is also required and is a positive number specifying the number of times to repeat the text in the first argument.

Let’s get charting!. And Create An In Cell Excel Chart

So, follow the steps below to create your in cell Excel chart.

Enter the REPT formula in the cell you want the chart to sit
Use a divisor to make the length of the bars smaller
Chnage the font to a more ‘chsrt like font’. I have used IMPACT
Adjust the colour of the font to fit your overall theme
Simple. An in cell chart with the REPT function in Excel

The formula I have used in this exmaple is

=REPT(“|”,B4/100) I have used 100 as the divisor to make the length of the cells more appropriate for the visual chart.

Make All Of Your Excel Charts The Same Size

February 11, 2022 by Barbara

Do you have a bunch of different-sized Excel charts on your worksheet? Do you need to know how to how to resize charts in Excel?. If so, you’re not alone. It’s easy to create multiple charts and then forget about the size until it’s too late. But don’t worry – we’ll show you how to make all of your Excel charts the same size in just a few simple steps. So follow along with us, and soon your charts will be uniformly sized and looking great!

This is a really useful tip if you have multiple charts embedded on a worksheet and they would look a lot better if they were all aligned and the same size. I use this a lot when presenting multiple charts. Simply follow the steps below to find out how to resize charts in Excel.

How To Resize Charts In Excel

First, select on of the charts that is the correct size you want. In my example it is the chart in the bottom right of the worksheet.
Click the chart to select it

Chart Tools>Format- note the height and width settings of the chart
Select CTL+Click the other three charts so all four are selected

Chart>Tools Format-enter in the height and width settings noted in the first step above
The charts will now be the same size see below

You can go ahead and manually align the charts or get Excel to do this for you

Chart Tools>Format>Arrange>Align

So, that’s all there is to it! You can make all of your charts the same size charts in Excel by following these simple steps. If you have any questions, or need more help with Excel, be sure to check out our other tutorials. We have everything from basic tips and tricks to advanced functions and formulas. Thanks for reading, and happy charting!

Formula Friday – Using Formulas To Add Custom Data Labels To Your Excel Chart

January 7, 2022 by Barbara

Happy Friday Excellers, welcome back to another blog post my #formulafriday blog series. Are you looking to add custom data labels to your Excel chart? Maybe you want to add the title of a song or the name of a magazine. Whatever the reason, it’s easy to do as long as you know how to use formulas. In this blog post, we’ll show you how to add custom data labels using formulas. We’ll also provide some tips on how to make sure your data labels look great on your chart. So, today let’s have a look at using a few types of formulas to add some really cool customised data labels to our Excel charts.

Why Use Excel Chart Labels?

Chart labels can help clarify the message you want to communicate within your data to your audiences. Consequently, you may want to add even more information to them and get some added value without over cluttering your chart.

The chart in our example shows April 2016 Sales Values for the six products we sell. So we can see our sales values for April 2016, but not much more.

Create Custom Excel Data Labels.

We could create some custom chart labels to show the value of sales and the difference in sales from the previous month. Let’s see how much more relevant detail we can pack into the chart for our audience.

Extra information is now added to the chart labels below. In this chart, there is not only current sales, but also that QRY1 is up 25% vs last month’s sales. Nice. Let’s create these labels then!

Let’s Create The Added Value Chart Data labels.

Firstly we need a helper column or two! These are custom Excel data labels. Some manipulation of the data is required. Below is the original data set for the Excel chart.

So, here is the same data set with two added helper columns D and E

Excel Chart Data Labels Using Formulas.

In Column D I added is the difference between April 2016 sales and the previous months sales, whereas in Column E is where I have my custom labels that appear on my chart.

The Formula is D is a standard % difference formula, taking the difference between the two numbers and dividing it by the original number. The full formula is in the screen grab below.

Finally, in Column E, I used the CONCATENATE Function to join the sales figures from April 2016 and add the resultant percentage difference between April 2016 and the previous month’s sales figures (from Column D). The result is the custom Excel data labels I need. The full formula used is in he screen grab below.

You can check my blog posts for more information on CONCATENATE and here for CONCATENATE for NUMBERS & PERCENTAGES.

Time To Create The Labels.

So, now it is time to generate the custom labels. The label generation is now a manual process, but it is worth it for those charts. Usually, real estate space on Excel charts is of a premium, and you want your chart to tell visually as much of the story as possible. Follow the steps below to create the custom data labels.

Select the chart label you want to change.
In the formula-bar hit = (equals), select the cell reference containing your chart label’s data.
In this case, the first label is in cell E2.
Finally, repeat for all your chart laebls.

If you are looking for a way to add custom data labels on your Excel chart, then this blog post is perfect for you. We will walk through how to create formulas that can be used in the future to quickly and effectively label any charts or graphs with specific information. So, if you need an easy way of adding extra context when presenting your data visually – read on!

Have you ever used these types of charting labels? Let me know in the comments below.

More Excel Tips.

Dont forget to sign up to the Excel at Excel Newletter for THREE free Excel hacks every month. Just click on the Sign Up Form to the right or use the link below.

Video Tutorial On Creating Self Expanding Charts

October 7, 2020 by Barbara

Hello, Excellers. Want to know how to create a Self Expanding Chart. Video tutorials are a great way to learn Excel. If you want to learn Excel you then I invite you to subscribe to my YouTube channels for lots of Tips and Tutorials.

Here is a free tutorial on creating self-expanding charts. It is one tip that readers liked very much so I have recorded a video tutorial. This is a simple and effective this technique is to add new data to Excel charts automatically.

So, in Excel 2007 onwards we can use a new table feature of Excel tables was introduced. By converting your data range to a table, the data for your Excel charts becomes dynamic. This means once new data is added to the table, any charts linked to that table also automatically update also.

Create A Self Expanding Chart.

If you prefer to read the corresponding blog post to this video, then click on the link below.

Create A Self Expanding Chart In Excel

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook.

Create A Self Expanding Chart In Excel

September 23, 2020 by Barbara

Hello Excellers, welcome back to another Excel tip today. I will show you how to create a self-expanding chart. This tip is also a YouTube Video. Why not check it out in the link below. So, it is really easy to create a self-expanding chart in Excel. Is it?. YES!. Normally a chart is set up to only display what is currently on the datasheet. But, by a few tweaks and Excel magic, we can set up a chart differently. We can allow a chart to automatically accommodate new data as it is added.

So, in Excel 2007 we can use a new table feature if you need the workbook to be backward compatible with previous Excel 2003 versions you need to do a few more clicks to get the same result. Let’s look at Excel 2007 and Excel 2003 first.

Create A Self Expanding Chart.

To create your self expanding chart in Excel 2007 and Excel 2003 do the following

Create your chart as usual ( you can use the sample data below)
Select any cell in the range that contains data that is used by the chart
Choose Insert- Tables Tables to convert the range to a table

self expanding chart in Excel

Note, if you are using Excel 2003 in Step 3 above choose Data- List-Create List.

Believe it or not, that’s all there is to it!!. Excel recognises the data as a table and that any data that is added to it, is automatically updated in the chart.

So, as you can see in the screenshot above Northern has been added in automatically after I typed it then tabbed along to the Widget Sales ready for the figure to be entered. A great way to create a self-expanding chart in Excel.

Finally, give this charting technique a go, it a good one to have in your Excel magic box of tricks.

How To Create An In Cell Bar Chart Using The REPT Function In Excel -Formula Friday

Did You Know About The REPT Function?

Let’s get charting!. And Create An In Cell Excel Chart

More Excel Charting And Formula Articles