How to Create a Legend in Excel Without a Chart (3 Steps)

Get FREE Advanced Excel Exercises with Solutions!

When dealing with Excel Charts, users usually insert Chart Legends using Chart’s options. However, in some cases, users need to create a legend in Excel without a Chart or Chart‘s options.

Let’s say we have Actual Monthly Sales data and Projected data. We want to create a legend without using the Excel Chart or its options. And the outcome should look like the below picture.

Direct Legend-How to Create a Legend in Excel without a Chart

This article demonstrates the step-by-step process of creating a legend in Excel without a Chart.


Excel Chart’s Legend and Its Insertion

A typical Chart’s Legend creation is pretty simple using Excel Chart options. If users have an inserted chart as shown in the below image,

Just click within the Chart area. The side menu appears.

Click on the Plus Icon, and Chart Elements appear.

Tick on Legend; the Chart differentiates the inserted lines with an identification known as the Legend.

Legend-How to Create a Legend in Excel without a Chart

Make sure you tick the Data Labels option to display each point. This is important because Data Labels are going to be used to directly insert Chart’s Legend in this article.

Go through the following steps to create direct Legends without Charts in Excel.


Step 1: Inserting Dummy Values to Create a Legend Without a Chart in Excel

Add a helper column adjacent to the dataset. Copy the immediate cell values and paste them into the helper column cells as shown in the image below.

Dummy Value

🔼 Insert a Line Chart > Display the Chart Elements (by clicking on the Plus Icon). You see, the Legend option is unticked, and the Chart displays no Legend whatsoever.

Legend

🔼 Click on the Chart then expand the Data Source range up to the dummy cells. You see, straight lines are added to the pre-existing lines, as depicted in the picture below.

Expanding Data Source

Read More: How to Show Legend with Only Values in Excel Chart (with Quick Steps)


Step 2: Custom Formatting Dummy Values as Legend Names

Place the cursor on the dummy value cells (here H4 and H5) and press CTRL+1 or right-click. The Format Cells or the Context Menu appears. In the case of the Context Menu, click Format Cells among the options. In the Format Cells window, select the Number section > Custom as Category > Type “Actual” (2nd time “Projected”) under Type > Click OK.

Format Cells-How to Create a Legend in Excel without a Chart

🔺 So, the final depiction of the dataset will be similar to the image below.

Formatted Data

Read More: How to Add a Data Table with Legend Keys in Excel


Step 3: Inserting a Chart with Direct Legends in Excel

As the inserted Chart has expanded the Data Source (expanded in the first step), Excel automatically creates the Legend identifying the Lines without using the Chart’s option.

Direct Legend-How to Create a Legend in Excel without a Chart

🔺 If you want to cross-check the situation, just click on the Chart > click on the Plus Icon that appears in the Side Menu > you will see the unticked Legend option. That verifies the inserting of the Legend without using Chart in Excel. Keep in mind that here the Data Labels option is used to create a Legend within the Chart. Make sure you tick the Data Labels option from the Chart Elements. To learn more about Data Labels, follow This Link.

Cross-Check

Read More: How to Reorder Legend Without Changing Chart in Excel


Download Excel Workbook


Conclusion

In this article, we demonstrate the step-by-step process in order to create a legend in Excel without a Chart. We use the Data Labels option available in the Chart Elements to display the Chart’s Legend without a Chart. We hope this method satisfies your quest and helps you achieve your goal. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo