How to rename a data series in microsoft excel

Format Data Labels in Excel: Overview

You can format data labels in Excel if you choose to add data labels to a chart. To format data labels in Excel, choose the set of data labels to format. To do this, click the “Format” tab within the “Chart Tools” contextual tab in the Ribbon. Then select the data labels to format from the “Chart Elements” drop-down in the “Current Selection” button group. Then click the “Format Selection” button that appears below the drop-down menu in the same area.

Alternatively, you can right-click the desired set of data labels to format within the chart. Then select the “Format Data Labels…” command from the pop-up menu that appears to format data labels in Excel. Using either method then displays the “Format Data Labels” task pane at the right side of the screen.

Format Data Labels in Excel- Instructions: A picture of the “Format Data Labels” task pane in Excel.

This task pane is where you format data labels in Excel. In the “Label Options” category, which is shown by default, you set the values and positioning of the data labels. You can also choose other formatting categories to display within the task pane. To do this, click the options to set, like the “Label Options” or “Text Options” choice. Then click the desired category icon to edit. The formatting options for the category then appear in collapsible and expandable lists at the bottom of the task pane.

Click the titles of each category list to expand and collapse the options within that category. Set any options you want within the task pane to immediately apply them to the chart. Then click the “X” in the upper-right corner of the task pane to close it.

Format Data Labels in Excel: Instructions

1. To format data labels in Excel, choose the set of data labels to format.
2. One way to do this is to click the “Format” tab within the “Chart Tools” contextual tab in the Ribbon.
3. Then select the data labels to format from the “Current Selection” button group.
4. Then click the “Format Selection” button that appears below the drop-down menu in the same area.
5. Alternatively, right-click the desired set of data labels to format within the actual chart.
6. Then select the “Format Data Labels…” command from the pop-up menu that appears to format data labels in Excel.
7. Using either method then displays the “Format Data Labels” task pane at the right side of the screen.
8. Set the values and positioning of the data labels in the “Label Options” category, which is shown by default.
9. To choose other formatting categories to show in the task pane, click the desired options to set.
10. Then click the desired category icon to edit.
11. The formatting options for the category appear in collapsible and expandable lists at the bottom of the task pane.
12. Click the titles of each category list to expand and collapse the display of the options in that category.
13. To immediately apply changes to the chart, set any options you want within the task pane.
14. Then click the “X” in the upper-right corner of the task pane to close it.

Format Data Labels in Excel: Video Lesson

The following video lesson, titled “ Formatting Data Labels ,” shows you how to format data labels in Excel. This video lesson is from our complete Excel tutorial , titled “ Mastering Excel Made Easy v.2019 and 365 .”

Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing. Read more.

A data series in Microsoft Excel is a set of data, shown in a row or a column, which is presented using a graph or chart. To help analyze your data, you might prefer to rename your data series.

Rather than renaming the individual column or row labels, you can rename a data series in Excel by editing the graph or chart. You might want to do this if your data labels are opaque and difficult to immediately understand.

You can rename any data series presented in a chart or graph, including a basic Excel bar chart.

To demonstrate, we have a basic bar chart showing a list of fruit sales on a quarterly basis. The chart shows four bars for each product, with the bars labeled at the bottom—these are your data series.

In this example, the data series are labeled alphabetically from A to D.

Labels such as these wouldn’t be hugely helpful for this example purpose, as you wouldn’t be able to determine the periods of time.

This is where you’d look to rename the data series. To do this, right-click your graph or chart and click the “Select Data” option.

This will open the “Select Data Source” options window. Your multiple data series will be listed under the “Legend Entries (Series)” column.

To begin renaming your data series, select one from the list and then click the “Edit” button.

In the “Edit Series” box, you can begin to rename your data series labels. By default, Excel will use the column or row label, using the cell reference to determine this.

Replace the cell reference with a static name of your choice. For this example, our data series labels will reflect yearly quarters (Q1 2019, Q2 2019, etc).

You could also replace this with another cell reference if you prefer to use labels that are separate from your data. This will ensure that your chart updates automatically if you decide to change the labels shown in those cells at a later date.

Once you’ve renamed your data series label, click “OK” to confirm.

This will take you back to the “Select Data Source” window, where you can repeat the steps for each data series label.

If you want to restore your labels to the same as your column or row labels, repeat the steps above, replacing the static labels with the cell reference for each column or row label.

You will need to name the worksheet containing the label when you do this. For instance, using =Sheet1!$B$1 here would show the label in cell B1.

For this example, this would show the letter A.

This will mean that any changes to your column or row labels will also update the data series labels in your chart or graph.

Once you’ve renamed all of the data labels, click “OK” to save the changes.

Your graph or chart will show your updated data series labels.

These are shown at the bottom of your chart, and are color-coded to match your data.

You can make further changes to the formatting of your graph or chart at this point. For instance, you could add a trendline to your Microsoft Excel chart to help you see further patterns in your data.

If you want to rename or edit the data series name in Microsoft Excel graph or chart without changing the row or column name, this article will be handy for you. The data series is the data that appears at the bottom of the graph or chart. It can be the row or column names.

It is straightforward to create a bar graph or chart, Line chart, etc. in an Excel spreadsheet. When you create a graph or chart, it fetches data from the selected cells. Let’s assume that you made a chart in an Excel spreadsheet, but you have to edit the data series name.

How to rename Data Series in Excel graph or chart

To change, edit or rename a Data Series name in Microsoft Excel Graph or Chart without editing the original row or column name, follow this procedure:

1. Open the Excel spreadsheet to find the chart.
2. Select the chart.
3. Right-click on it and choose the Select Data option.
4. Select the data series from the Legend Entries box.
5. Click the Edit button.
6. Enter the new name in the Series name box.
7. Enter the Series values if required.
8. Click the OK button.

Open up the Excel spreadsheet where you can find the desired chart. Then, select the chart in the spreadsheet and right-click on it. In the context menu, you will see an option called Select Data. You need to click on it.

After that, choose the data series name that you want to change in the Legend Entries box. It appears on the left-hand side of the window. Following that, click the Edit button.

Now, remove everything from the Series name box and enter the new name that you want to show in the chart. Following that, enter the Series values if you want to customize it as well.

Once done, click the OK button twice to save the change. After saving, the spreadsheet chart or graph will appear with the new data series name.

In case you want to change multiple data series names, follow the same steps. Before clicking the second OK button to save the change, select the next data series, and keep following the same steps as mentioned here.

If you want to rename or edit the data series name in Microsoft Excel graph or chart without changing the row or column name, this article will be handy for you. The data series is the data that appears at the bottom of the graph or chart. It can be the row or column names.

It is straightforward to create a bar graph or chart, Line chart, etc. in an Excel spreadsheet. When you create a graph or chart, it fetches data from the selected cells. Let’s assume that you made a chart in an Excel spreadsheet, but you have to edit the data series name.

How to rename Data Series in Excel graph or chart

To change, edit or rename a Data Series name in Microsoft Excel Graph or Chart without editing the original row or column name, follow this procedure:

1. Open the Excel spreadsheet to find the chart.
2. Select the chart.
3. Right-click on it and choose the Select Data option.
4. Select the data series from the Legend Entries box.
5. Click the Edit button.
6. Enter the new name in the Series name box.
7. Enter the Series values if required.
8. Click the OK button.

Open up the Excel spreadsheet where you can find the desired chart. Then, select the chart in the spreadsheet and right-click on it. In the context menu, you will see an option called Select Data. You need to click on it.

After that, choose the data series name that you want to change in the Legend Entries box. It appears on the left-hand side of the window. Following that, click the Edit button.

Now, remove everything from the Series name box and enter the new name that you want to show in the chart. Following that, enter the Series values if you want to customize it as well.

Once done, click the OK button twice to save the change. After saving, the spreadsheet chart or graph will appear with the new data series name.

In case you want to change multiple data series names, follow the same steps. Before clicking the second OK button to save the change, select the next data series, and keep following the same steps as mentioned here.

After you create a chart, you can change the data series in two ways:

Use the Select Data Source dialog box to edit the data in your series or rearrange them on your chart.

Use chart filters to show or hide data in your chart.

Edit or rearrange a series

Right-click your chart, and then choose Select Data.

In the Legend Entries (Series) box, click the series you want to change.

Click Edit, make your changes, and click OK.

Changes you make may break links to the source data on the worksheet.

To rearrange a series, select it, and then click Move Up or Move Down .

You can also add a data series or remove them in this dialog box by clicking Add or Remove. Removing a data series deletes it from the chart—you can’t use chart filters to show it again.

If you want to rename a data series, see Rename a data series.

Filter data in your chart

Let’s start with chart filters.

Click anywhere in your chart.

Click the Chart Filters button next to the chart.

On the Values tab, check or uncheck the series or categories you want to show or hide.

If you want to edit or rearrange the data in your series, click Select Data, and then follow steps 2-4 in the next section.

Edit or rearrange a series

Click on the chart.

On the ribbon, click Chart Design and then click Select Data.

This selects the data range of the chart and displays the Select Data Source dialog box.

To edit a legend series, in the Legend entries (series) box, click the series you want to change. Then, edit the Name and Y values boxes to make any changes.

Note: Changes you make may break links to the source data on the worksheet.

To rearrange a legend series, in the Legend entries (series) box, click the series you want to change and then select the or arrows.

Filter data in your chart

In Word and PowerPoint: Select your chart and then on the Chart Design tab, click Edit Data in Excel.

In Excel, select the category title and then in the Home tab, click Sort & Filter > Filter.

Next, click the drop-down arrow to select the data you want to show, and deselect the data you don’t want to show.

If your chart contains chart titles (ie. the name of the chart) or axis titles (the titles shown on the x, y or z axis of a chart) and data labels (which provide further detail on a particular data point on the chart), you can edit those titles and labels.

You can also edit titles and labels that are independent of your worksheet data, do so directly on the chart and use rich-text formatting to make them look better.

Note that you can edit titles and data labels that are linked to worksheet data in the corresponding worksheet cells. If, for example, you change the title in a cell from “Yearly Revenue” to “Annual Revenue” — that change will automatically appear in the titles and data labels on the chart. You won’t, however, be able to use rich-text formatting when you make a change from within a cell.

When you edit a linked title or data label on the chart (instead of within a cell), that title or data label will no longer be linked to the corresponding worksheet cell, and the changes that you make are not displayed in the worksheet itself (although you will see them on the chart). However, you can reestablish links between titles or data labels and worksheet cells.

After you finish editing the text, you can move the data labels to different positions as needed.

Note: To make any of the changes described below, a chart must already have titles or data labels. To learn to add them, see Add or remove titles in a chart and Add or remove data labels in a chart.

What do you want to do?

Edit the contents of a title or data label on the chart

On a chart, do one of the following:

To edit the contents of a title, click the chart or axis title that you want to change.

To edit the contents of a data label, click two times on the data label that you want to change.

The first click selects the data labels for the whole data series, and the second click selects the individual data label.

Click again to place the title or data label in editing mode, drag to select the text that you want to change, type the new text or value.

To insert a line break, click to place the cursor where you want to break the line, and then press ENTER.

When you are finished editing, click outside of the text box where you have made your text changes.

To format the text in the title or data label box, do the following:

Click in the title box, and then select the text that you want to format.

Right-click inside the text box and then click the formatting options that you want.

You can also use the formatting buttons on the Ribbon ( Home tab, Font group). To format the whole title, you can right-click it, click Format Chart Title, and then select the formatting options that you want.

Note: The size of the title or data label box adjusts to the size of the text. You cannot resize the title or data label box, and the text may become truncated if it does not fit in the maximum size. To accommodate more text, you may want to use a text box instead. For more information, see Add a text box to a chart.

Edit the contents of a title or data label that is linked to data on the worksheet

In the worksheet, click the cell that contains the title or data label text that you want to change.

Edit the existing contents, or type the new text or value, and then press ENTER.

The changes you made automatically appear on the chart.

Reestablish the link between a title or data label and a worksheet cell

Links between titles or data labels and corresponding worksheet cells are broken when you edit their contents in the chart. To automatically update titles or data labels with changes that you make on the worksheet, you must reestablish the link between the titles or data labels and the corresponding worksheet cells. For data labels, you can reestablish a link one data series at a time, or for all data series at the same time.

In PivotChart reports, the following procedures reestablish links between data labels and source data (not worksheet cells).

Reestablish the link for a chart or axis title

On a chart, click the chart or axis title that you want to link to a corresponding worksheet cell.

On the worksheet, click in the formula bar, and then type an equal sign (=).

Select the worksheet cell that contains the data or text that you want to display in your chart.

You can also type the reference to the worksheet cell in the formula bar. Include an equal sign, the sheet name, followed by an exclamation point; for example, =Sheet1!F2

Reestablish the link for a data label

When you customize the contents of a data label on the chart, it is no longer linked to data on the worksheet. You can reestablish the link by resetting the label text for all labels in a data series, or you can type a reference to the cell that contains the data that you want to link to for each data point at a time.

Reset label text

On a chart, click one time or two times on the data label that you want to link to a corresponding worksheet cell.

The first click selects the data labels for the whole data series, and the second click selects the individual data label.

Right-click the data label, and then click Format Data Label or Format Data Labels.

Click Label Options if it’s not selected, and then select the Reset Label Text check box.

Reestablish a link to data on the worksheet

On a chart, click the label that you want to link to a corresponding worksheet cell.

On the worksheet, click in the formula bar, and then type an equal sign (=).

Select the worksheet cell that contains the data or text that you want to display in your chart.

You can also type the reference to the worksheet cell in the formula bar. Include an equal sign, the sheet name, followed by an exclamation point; for example, =Sheet1!F2

Change the position of data labels

You can change the position of a single data label by dragging it. You can also place data labels in a standard position relative to their data markers. Depending on the chart type, you can choose from a variety of positioning options.

On a chart, do one of the following:

To reposition all data labels for an entire data series, click a data label once to select the data series.

To reposition a specific data label, click that data label twice to select it.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Layout tab, in the Labels group, click Data Labels, and then click the option that you want.

For additional data label options, click More Data Label Options, click Label Options if it’s not selected, and then select the options that you want.

Mahesh Makvana is a freelance tech writer who specializes in writing how-to guides. He has been writing tech tutorials for over a decade now. He’s written for some of the prominent tech sites including MakeUseOf, MakeTechEasier, and Online Tech Tips. Read more.

By default, Microsoft Excel names your tables “Table1”, “Table2”, and so on. If you’d prefer custom names, you can rename your tables in Excel. It’s easy, and it works similarly to assigning names to cell ranges.

Supported Table Names in Microsoft Excel

Excel has certain rules for naming tables, and you must adhere to these while giving names to your tables:

• Starting Character: Your table’s name must start with a character, an underscore (_), or a backslash (\). Also, you can’t use “C”, “c”, “R”, or “R” as your table name as these characters are designated for other purposes in Excel.
• Cell Reference: You can’t use a cell reference as your table name.
• Name Separator: You can’t separate multiple words in your table name with a space. To do that, use either an underscore or a dot.
• Character Limit: Your table name must fit the 255 maximum character limit.
• No Same Names: You can’t have two tables with the same name in your workbook. Also, Excel considers uppercase and lowercase table names the same, so you can’t use “MYTABLE” if you have used “mytable” in your workbook already.

When you rename your table and you miss one of the above points, Excel will alert you with an error message so you can fix the issue.

How to Rename a Table in Microsoft Excel

To give a name to your table, first, open your spreadsheet with Microsoft Excel.

In your spreadsheet, click any cell of the table you want to rename.

While your cell is selected, in Excel’s ribbon at the top, click the “Table Design” tab. If you are on a Mac, click “Table” instead.

On the “Table Design” tab, in the “Properties” section, click the “Table Name” field. Then type a new name for your table and press Enter.

Your table now uses your specified name, and this is the name you will use to refer to it from other cells in your spreadsheet.

And you’re all set.

Need to add or remove rows or columns from your Excel table? It’s equally easy to do that.

When you create a chart in Excel, it uses the information in the cell above each column or row of data as the legend name. You can change legend names by updating the information in those cells, or you can update the default legend name by using Select Data.

Note: If you create a chart without using row or column headers, Excel uses default names, starting with “Series 1.” You can learn more about how to create a chart to ensure your rows and columns are formatted properly.

In this article

Show the legend in your chart

Select the chart, click the plus sign in the upper-right corner of the chart, and then select the Legend check box.

Optional. Click the black arrow to the right of Legend for options on where to place the legend in your chart.

Change the legend name in the excel data

Select the cell in the workbook that contains the legend name you want to change.

Tip: You can first click your chart to see what cells within your data are included in your legend.

Type the new legend name in the selected cell, and press Enter. The legend name in the chart updates to the new legend name in your data.

Certain charts, such as Clustered Columns, also use the cells to the left of each row as legend names. You can edit legend names the same way.

Change the legend name using select data

Select your chart in Excel, and click Design > Select Data.

Click on the legend name you want to change in the Select Data Source dialog box, and click Edit.

Note: You can update Legend Entries and Axis Label names from this view, and multiple Edit options might be available.

Type a legend name into the Series name text box, and click OK. The legend name in the chart changes to the new legend name.

Note: This modifies your only chart legend names, not your cell data. Alternatively, you can select a different cell in your data to use as the legend name. Click inside the Series name text box, and select the cell within your data.

When a chart has a legend displayed, you can modify the individual legend entries by editing the corresponding data on the worksheet. For additional editing options, or to modify legend entries without affecting the worksheet data, you can make changes to the legend entries in the Select Data Source dialog box.

What do you want to do?

Edit legend entries on the worksheet

On the worksheet, click the cell that contains the name of the data series that appears as an entry in the chart legend.

Type the new name, and then press ENTER.

The new name automatically appears in the legend on the chart.

Edit legend entries in the Select Data Source dialog box

Click the chart that displays the legend entries that you want to edit.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Design tab, in the Data group, click Select Data.

In the Select Data Source dialog box, in the Legend Entries (Series) box, select the legend entry that you want to change.

Tip: To add a new legend entry, click Add, or to remove a legend entry, click Remove.

In the Series Name box, do one of the following:

Type the reference to the worksheet cell that contains the data that you want to use as the legend entry text.

Tip: You can also click the Collapse Dialog button at the right end of the Series name box, and then select the worksheet cell that contains the data that you want to use as the legend entry. When you finish, click the Collapse Dialog button again to display the entire dialog box.

Type the legend entry name that you want to use.

Note: When you type a new name, the legend entry text is no longer linked to data in a worksheet cell.

To add a new legend entry, type the reference to the worksheet cell or type a new name and then, in the Series values box, type a reference to the data series on the worksheet that you want to use for the new legend entry.

Tip: You can also click the Collapse Dialog button at the right end of the Series values box, and then select the data series that you want to use for the new legend entry. When you finish, click the Collapse Dialog button again to display the entire dialog box.

If you want to rename or edit the data series name in Microsoft Excel graph or chart without changing the row or column name, this article will be handy for you. The data series is the data that appears at the bottom of the graph or chart. It can be the row or column names.

It is straightforward to create a bar graph or chart, Line chart, etc. in an Excel spreadsheet. When you create a graph or chart, it fetches data from the selected cells. Let’s assume that you made a chart in an Excel spreadsheet, but you have to edit the data series name.

How to rename Data Series in Excel graph or chart

To change, edit or rename a Data Series name in Microsoft Excel Graph or Chart without editing the original row or column name, follow this procedure:

1. Open the Excel spreadsheet to find the chart.
2. Select the chart.
3. Right-click on it and choose the Select Data option.
4. Select the data series from the Legend Entries box.
5. Click the Edit button.
6. Enter the new name in the Series name box.
7. Enter the Series values if required.
8. Click the OK button.

Open up the Excel spreadsheet where you can find the desired chart. Then, select the chart in the spreadsheet and right-click on it. In the context menu, you will see an option called Select Data. You need to click on it.

After that, choose the data series name that you want to change in the Legend Entries box. It appears on the left-hand side of the window. Following that, click the Edit button.

Now, remove everything from the Series name box and enter the new name that you want to show in the chart. Following that, enter the Series values if you want to customize it as well.

Once done, click the OK button twice to save the change. After saving, the spreadsheet chart or graph will appear with the new data series name.

In case you want to change multiple data series names, follow the same steps. Before clicking the second OK button to save the change, select the next data series, and keep following the same steps as mentioned here.

By default, tables will be automatically named with a series name, such as Table1, Table2… etc. in Excel. It’s not convenient if you need to look for or refer to tables by table name. Here, this article will guide you to rename a table in Excel.

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Rename a table in Excel

It’s very easy to rename a table in Excel. Please do as follows:

1. Click any cell in the table to activate the Table Tools.

2. Go to the Properties group on the Design tab, please type the new table name in the Table name box, and press the Enter key. See screenshot:

Using multiple worksheets in an Excel workbook is an easy way to keep your data organized. For example, you can have different sheets for different regions, or for different months/year, or different projects, etc.

And when working with multiple sheets, you may sometimes need to change their names.

In this Excel tutorial, I will show you some simple ways to rename a sheet in Excel (including a keyboard shortcut).

And in case you want to rename all the sheets as one go (such as adding a prefix or suffix to all the sheets), check out the section on using VBA to do this (it’s not complicated).

So let’s get started!

This Tutorial Covers:

Rename Sheets with a Simple Double Click

Using a mouse is the easiest way to change the name of a sheet in Excel.

Below are the steps to rename a sheet using the mouse double-click:

1. Place your cursor over the sheet tab that you want to rename
2. Double-click on it. This will put the sheet name in the edit mode
3. Enter the name of the sheet that you want
4. Hit the Enter key (or click anywhere in the worksheet)

If you want to rename multiple sheets, you need to do these above steps for all the sheets.

Also, if you want to add a prefix/suffix to the sheet name, you the arrow keys to move the cursor when you’re in the edit mode in the tab. Once you have the cursor where you want to add the text, enter in manually.

Rename Sheets with a Keyboard Shortcut

If you prefer using the keyboard and don’t want to switch to a mouse just to rename a sheet, you can also use the following keyboard shortcut (for Windows).

You need to press these keys one after the other in a succession.

While this is not one of the easy keyboard shortcuts, once you get used to it, you may find it a better way to rename sheets in Excel.

Rename Sheets Using the Format Option in the Ribbon

You can also access the option to rename sheets through the Excel ribbon:

1. Click the Home tab
2. In the Cell group, click on the ‘Format’ option
3. Click on the Rename Sheet option. This will get the sheet name into edit mode
4. Enter the name that you want for the sheet

This method would only allow you to change the name of the current sheet. To change the name of some other sheet, you first need to activate it and then use the above steps.

While this method exists, I always prefer using the mouse double-click technique to change the name of the sheet.

Rename Sheets Using VBA – Add Prefix/Suffix to All Sheet Names in One Go

Sometimes, you may have a need to add a prefix or a suffix to all the sheets in your workbook.

While you can do this manually (using the methods shown above) if you only have a few worksheets. But in case you have a lot many, this could be tedious and error-prone.

In such a case, you can use a simple VBA code in the immediate window to get this done in a snap.

Below is the VBA code that will add the prefix “2020 – ‘ in front of all the sheet names:

The above code uses a VBA loop (the For Next loop) to go through each sheet in the workbook and change the name by adding the specified prefix.

Below are the steps to use this VBA macro code in Excel:

1. Right-click on any of the sheet tabs
2. Click on View Code. This will open the Visual Basic Editor backend
3. In the VB Editor window, click on the ‘View’ option in the menu and then click on Immediate Window. This will make the immediate window show up. You don’t need to do this in case the immediate window is already visible.
4. Copy and paste the above line of code in the immediate window
5. Place the cursor at the end of the line
6. Hit the Enter key

As soon as you hit the Enter key, the code is executed and it adds the specified prefix in all the sheet names.

Note that this will rename all the sheets – visible as well as hidden.

Similarly, if you want to add a suffix to all the sheet names, you can use the below code:

You can even go a step further and also change the name of some specific sheets (let’s say only the ones where the text 2020 appears). This can easily be done by editing the code.

Sheet Naming Rules in Excel

When renaming a sheet in Excel, there are some naming rules you need to follow:

1. A sheet name can be a maximum of 31 characters in length.
2. You can not leave a sheet name blank.
3. You can not have two sheets of the same name.
4. You can not begin or end the sheet name with an apostrophe (‘). However, you can use it in the middle somewhere in the sheet name
5. You can not use the following characters while renaming the sheet in Excel: [ ] \ / ? : *
   • This means that you can’t have a sheet name with a date that uses a forward slash. For example, 01/01/2020 isn’t a valid sheet name but 01-01-2020 is valid.
6. You can not name the sheet History (as it’s used internally by Excel)

I hope you found this Excel tutorial useful.

You may also like the following Excel tips and tutorials:

How to Modify Chart Data in Excel

Once you see data in a chart, you may find there are some tweaks and changes that need to be made. Here are a few ways to change the data in your chart.

Add a Data Series

If you need to add additional data from the spreadsheet to the chart after it’s created, you can adjust the source data area.

1. Select the chart.
2. In the worksheet, click a sizing handle for the source data and drag it to include the additional data.

The new data needs to be in cells adjacent to the existing chart data.

Rename a Data Series

Charts are not completely tied to the source data. You can change the name and values of a data series without changing the data in the worksheet.

1. Select the chart
2. Click the Design tab.
3. Click the Select Data button.

The name is updated in the chart, but the worksheet data remains unchanged.

Reorder a Data Series

You can also change the order of data in the chart without changing the order of the source data.

1. Select the chart
2. Click the Design tab.
3. Click the Select Data button.

The chart is updated to display the new order of data, but the worksheet data remains unchanged.

FREE Quick Reference

Free to distribute with our compliments; we hope you will consider our paid training.

In Excel, how could you copy a worksheet and rename it automatically without typing the name as usual? In this article, I will talk about how to copy a sheet one time or multiple times and give them specific names at once.

Copy a worksheet multiple times and rename them with increment tab names by using VBA code

If you need to copy a worksheet multiple times, and give them series names, such as KTE-1, KTE-2, KTE-3, KTE-4…, the following VBA code can do you a favor.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy a worksheet multiple times and rename at once:

3. Then press F5 key to run this code, and a prompt box will remind you how many copies of the current worksheet that you want to copy, see screenshot:

4. Enter the number of copies into the popped dialog, and then click OK button, your current worksheet has been duplicated 5 copies and renamed with sequence name as following screenshot shown:

Note: In the above code, you can change the worksheet name text “ KTE- ” to your needed.

Copy a worksheet multiple times and rename them with custom names by using Kutools for Excel

Maybe the above VBA codes are difficult for you to apply, here, I will introduce an easy tool- Kutools for Excel, with its Create Sequence Worksheets utility, you can achieve this task without any effort.

After installing Kutools for Excel, please do as follows:

1. Click Kutools Plus > Worksheet > Create Sequence Worksheets, see screenshot:

2. In the Create Sequence Worksheets dialog box:

(1.) Select the worksheet name that you want to copy from the Base Worksheet drop down list;

(2.) Then specify the number of worksheet that you want to copy under The number of create text box;

(3.) And then check Sequence Numbers under the Sheet names based on section;

(4.) At last, create the sequence worksheet names in the right pane that you want, see screenshot:

3. After finishing the settings, please click Ok button, and the specific worksheet has been copied multiple times and renamed based on your created sequence in a new workbook, see screenshot:

How to Modify Chart Data in Excel

Once you see data in a chart, you may find there are some tweaks and changes that need to be made. Here are a few ways to change the data in your chart.

Add a Data Series

If you need to add additional data from the spreadsheet to the chart after it’s created, you can adjust the source data area.

1. Select the chart.
2. In the worksheet, click a sizing handle for the source data and drag it to include the additional data.

The new data needs to be in cells adjacent to the existing chart data.

Rename a Data Series

Charts are not completely tied to the source data. You can change the name and values of a data series without changing the data in the worksheet.

1. Select the chart
2. Click the Design tab.
3. Click the Select Data button.

The name is updated in the chart, but the worksheet data remains unchanged.

Reorder a Data Series

You can also change the order of data in the chart without changing the order of the source data.

1. Select the chart
2. Click the Design tab.
3. Click the Select Data button.

The chart is updated to display the new order of data, but the worksheet data remains unchanged.

FREE Quick Reference

Free to distribute with our compliments; we hope you will consider our paid training.

In my (quite complex) Excel workbook I’m using a lot of user defined functions. This works without problems.

Now I’d like to rename several of those functions, because the some of the function names chosen initially are not very good. If I rename them naively in the VBA editor, the workbook doesn’t work anymore, because the names of the user defined functions in the formulas in the workbook are not renamed automatically.

I now have only two possibilities:

1. leaving the function names as they are and cope with it
2. renaming the functions manually in every single formula in all worksheets of my workbook.

Is there a more effective way to to rename all user defined functions in all formulas ?

2 Answers 2

Go with 2 – having well-named functions is very good practice (you might know what they all mean now, but what about in 6 months time, or when someone else has to look after it?)

First job – rename the function. Easy enough. Second, find everywhere in the VBA that the function is called. Hit Ctrl+F , select Current Project and search for the function name.

Finally, you can do essentially the same on the workbook. Hit Ctrl-F , go to the Replace tab. Check that Within is set to Workbook and Look in is set to Formulas.

Since this is StackOverflow, I’d feel remiss not posting a VBA solution for Find & Replace in the workbook:

However, be careful what values you’re replacing – if you have function names like Function a() , then doing a find & replace on a is likely to cause all kinds of problems.

Normally, we can easily rename a worksheet with right clicking the sheet in the Sheet Tab and selecting Rename from right-clicking menu in Excel. What if renaming all/multiple worksheets with specified cell values? And what if dynamically naming a worksheet based on a specified cell value? In this article, a couple of methods will help you solve them easily.

• Dynamically name a sheet based on cell value with VBA
• Name multiple sheets based on cell values in corresponding sheets
• Name multiple sheets from the specified list

Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

We can dynamically name a worksheet from a certain cell’s value with VBA in Excel, and you can do as follows:

Step 1: Right click the worksheet in the Sheet Tab that you will dynamically name it by a cell value, and select View Code from the right-clicking menu.

Step 2: In the opening Microsoft Visual Basic for Applications window, paste the following VBA code into the module window.

Note: This VBA can dynamically rename the worksheet based on the value of Cell A1, and you can change the A1 to other cells based on your needs.

Step 3: Save the VBA code and close the Microsoft Visual Basic for Application window.

Then you will see the sheet name is dynamically changed based on the specified cell value.

Notes:

1. If the specified cell is blank, the worksheet won’t be renamed;
2. If you type special characters into the specified cell, such as *, it will pop up an error warning.

With Kutools for Excel’s Rename Multiple Worksheets feature, you can quickly name all/multiple sheets with the specified cell in corresponding sheet. For example, rename each sheet with the Cell A1 value in each sheet.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

Kutools for Excel – Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Kutools Plus > Worksheet > Rename Worksheets. See screenshot:

2. In the opening Rename Multiple Worksheets dialog box, please (see screenshot below):

(1) In the Worksheets list, please check the worksheet whose name you will change;
(2) In the Rename Options section, please check the Replace original sheet name option;
(3) In the New Worksheet Name section, please check the Rename worksheets with specific cell option, and specify the cell whose content you will name corresponding sheet with.
(4) Click the Ok button.

And now all checked sheets have been renamed with the specified cell of each sheet. See screenshot:

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Kutools for Excel’s Rename Multiple Worksheets feature also supports to name multiple sheet with cell values in the specified range.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

Kutools for Excel – Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click the Kutools Plus > Worksheet > Rename Worksheets.

2. In the opening Rename Multiple Worksheets dialog box, you need to (see screenshots below):

(1) In the Worksheets list, check the worksheet you will rename with cell values.
(2) In the Rename Options section, check the Replace original sheet name option;
(3) In the New Worksheet Name section, check the From Specific range option, click the button to open the second Rename Multiple Worksheets dialog box, select the cells that you will rename by their values, and click the OK button.
(4) Click the Ok button to apply the renaming.

Then you will see all checked worksheet names are changed to the specified cell values. See below screen shot:

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

How do I download of tab-delimited water data into Microsoft Excel?

Many users have found that when they retrieved the data in tab-delimited format (rdb) they can create an Excel macro to transform the information so it can be rendered easily into MS Excel. Sorry, we cannot program your macros for you.

• Excel 2007/2010: Select the Data tab, the Get External Data Group, and then the From Web button. Follow the prompts.
• Excel 2003: Use the Web Query Tool, which can be found under the Data menu. Choose the Import External Data Menu option under that menu.

More advanced users will find ways of using Visual Basic for Applications (VBA) available in Excel to do programmatic logic. In most cases, it is difficult to fully automate the process.

How do I convert a tab-delimited RDB file to a Microsoft Excel spreadsheet?

Instructions for Excel 2007 and Excel 2010:

• When prompted by your browser, save the downloaded file with a .txt extension. If you are not prompted, after the file is downloaded rename it to give it a .txt extension.
• Start Microsoft Excel 2007 or Excel 2010.
• For Excel 2007, click on the Office Button, then Open. For Excel 2010, click on the File tab, then Open.
• Select the file, then press the Open button.
• A Text Import Wizard should appear. Select all the defaults. (It should be a three-step process.)
• You may choose to adjust the columns to see data in certain columns. If the text is too big to fit in the column, you will see a series of # signs.
• Add or delete rows and columns if you wish
• To save as a spreadsheet:
  • If Office 2007, press the Office Button. In Office 2010, press the File tab.
  • Select Save As
  • Navigate to the drive and/or folder where you want to save the data
  • Select “Microsoft Office Excel Workbook”, and then press Save. Note that the file will have a .xlsx extension. The original file should still be available with a .txt extension.

Instructions for Excel 2003:

• When prompted by your browser, save the file with a .txt extension. If you are not prompted, after the file is downloaded rename it to give it a .txt extension.
• Start Microsoft Excel 2003.
• Select File on the menu, then Open under the File menu
• In the “Files of Type” control, select Text Files
• Use the “Look In” control to navigate to the folder containing the file you downloaded
• Select the file, then press the Open button
• A Text Import wizard should appear. Select all the defaults. (It should be a three-step process.)
• You may choose to adjust the columns to see data in certain columns. If the text is too big to fit in the column, you will see a series of # signs.
• Add or delete rows and columns if you wish
• To save as a spreadsheet:
  • Select File on the menu
  • Select Save As under the File menu.
  • In the “Save as Type” control, select “Microsoft Office Excel Workbook”
  • Press Save. The original file should still be available with a .txt extension.

When you add a field to a pivot table, the name of the field in the pivot table comes from the column name in the source data. However, you can manually change the name to almost anything you like.

Let’s take a look.

Here we have a blank pivot table. Let’s add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the type of calculation being used on the field. In this case, we get Sum of Total Sales. If we change the calculation to Count, the field is automatically renamed to Count of Total Sales.

However, we can rename the field as we like. For example, we can rename this field to Sales. For value fields, the main restriction is that you can’t use any field names already in the source data. For example, if we try to rename the field to Total Sales, we get a warning that the pivot table field name already exists.

If you really want to name a value field to the same name that appears in the source data, you can add a space character to the end of the field name. Visually, the field looks the same, and Excel won’t complain about duplicate field names.

When you rename field names, you may want to confirm the original name from the source data. You can do this by accessing Field Settings for the field in question. Either right-click on the field and choose Value field settings, or click Field Settings on the Options Tab of the PivotTable Tools ribbon. Here, you can see the original field name.

In contrast to value fields, Row and Column label field names will be identical to the name in the field list. In fact, they are linked, as we’ll see in a minute.

To illustrate how this works, let’s add Category as a row label and Region as a Column label, then rename the fields.

When you add a field as a row or column label, you’ll see the same name appear in the Pivot table. You’re free to type over the name directly in the pivot table.

You can also use the Field Settings dialog box to rename the field.

When you rename fields used in Columns or Rows, the name also changes in the field list. In fact, even if you remove the field and refresh the pivot table, your new name will stick around.

To go back to the original name, re-enter the field name manually. Alternately, you can clear the pivot table and start again.

Closed 3 years ago .

• We don’t allow questions about general computing hardware and software on Stack Overflow. You can edit the question so it’s on-topic for Stack Overflow.
• Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

How does one reorder series used to create a chart in Excel?

For example, I go to the chart, right click > Select Data. In the left column I see series 1, series 2, to series n.

Say, I want to move series 3 after series 4, can it be done from chart view? I don’t want to move the data cells in the worksheet.

I’m using Excel 2011 (mac OS X).

9 Answers 9

Select a series and look in the formula bar. The last argument is the plot order of the series. You can edit this formula just like any other, right in the formula bar.

For example, select series 4, then change the 4 to a 3.

Right-click any series on the chart. In the “Format Data Series” dialog, there is a “Series Order” tab, in which you can move series up and down. I find this much easier than fiddling with the last argument of the series formula.

This is in Excel 2003 in Windows. There is a similar dialog in Excel 2011 for Mac:

These are the buttons UP/DOWN

Use the below code, If you are using excel 2007 or 2010 and want to reorder the legends only. Make sure mChartName matched with your chart name.

Excel 2010 – if you’re looking to reorder the series on a pivot chart:

• go to your underlying pivot table
• right-click on one of the Column Labels for the series you’re looking to adjust (Note: you need to click on one of the series headings (i.e. ‘Saturday’ or ‘Sunday’ in the example shown below) not the ‘Column Labels’ text itself)
• in the pop-up menu, hover over ‘Move’ and then select an option from the resulting sub-menu to reposition the series variable.
• your pivot chart will update itself accordingly

FYI, if you are using two y-axis, the order numbers will only make a difference within the set of series of that y-axis. I believe secondary -y-axis by default are on top of the primary. If you want the series in the primary axis to be on top, you’ll need to make it secondary instead.

To change the stacking order for series in charts under Excel for Mac 2011:

1. select the chart,
2. select the series (easiest under Ribbon>Chart Layout>Current Selection),
3. click Chart Layout>Format Selection or Menu>Format>Data Series …,
4. on popup menu Format Data Series click Order, then click individual series and click Move Up or Move Down buttons to adjust the stacking order on the Axis for the subject series. This changes the order for the plot and for the legend, but may not change the order number in the Series formula.

I had a three series plot on the secondary axis, and the series I wanted on top was stuck on the bottom in defiance of the Move Up and Move Down buttons. It happened to be formatted as markers only. I inserted a line, and presto(!), I could change its order in the plot. Later I could remove the line and sometimes it could still be ordered, but sometimes not.

Summary

In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged in some way. When the source data to a chart is lost, the data can still be retrieved from the chart itself, by using a Microsoft Visual Basic for Applications macro.

More Information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

The following sample macro places the chart’s source data in a worksheet called “ChartData” in the active workbook, beginning in the first column and first row.

Enter the following macro code in a module sheet:

Insert a new worksheet into your workbook and rename it to “ChartData” (without the quotation marks).

Select the chart from which you want to extract the underlying data values.

The chart can either be embedded on a worksheet or on a separate chart sheet.

Run the GetChartValues macro.

The data from the chart is placed in the “ChartData” worksheet.

Steps to Link the Chart to the Recovered Data

To have the chart be interactive with the recovered data, you need to link the chart to the new data sheet rather than retain the links to the missing or damaged workbook.

Select the chart, and click a series to find the sheet name to which the chart is linked in the damaged or missing workbook. The sheet name appears in the series formula in the formula bar.

The sheet name may follow the workbook name, which is enclosed in square brackets such as “[Book1],” and precede the exclamation point “!” (or apostrophe and exclamation point “‘!”) that indicates the beginning of a cell reference. The sheet name includes only the characters between the closed square bracket symbol “]” and the exclamation point (or apostrophe and exclamation point). Leave out any apostrophe if it is immediately before the exclamation mark, because an apostrophe can not be the last character in a sheet name.

Double-click the tab of the new sheet called ChartData.

Type the original sheet name from step 1 over the highlighted “ChartData” and press ENTER. This name must be the same as the sheet name from the damaged or missing workbook.

If you have not saved this file with the chart and data sheet, save the file.

In Excel 2003 or Excel 2002, click Links on the Edit menu, and then click Change Source.

In Excel 2007, click the Data tab, click Edit Links in the Connenctions group, and then click Change Source.

In the Source File box, select the link to change, and then click Change Source.

In the Change Links dialog box, select the new file with the recovered data and chart, and then click OK.

If you receive the following error message

Your formula contains an invalid external reference to a worksheet.

it is likely that the sheet name that you typed in step 3 is not the same as the original. Go back to step 1.

The Source File box may now be blank. This indicates that all links point to the active file rather than the missing or damaged file. Click Close.

The chart now references and interacts with the recovered data on the renamed sheet in the active workbook.

When you add a field to a pivot table, the name of the field in the pivot table comes from the column name in the source data. However, you can manually change the name to almost anything you like.

Let’s take a look.

Here we have a blank pivot table. Let’s add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the type of calculation being used on the field. In this case, we get Sum of Total Sales. If we change the calculation to Count, the field is automatically renamed to Count of Total Sales.

However, we can rename the field as we like. For example, we can rename this field to Sales. For value fields, the main restriction is that you can’t use any field names already in the source data. For example, if we try to rename the field to Total Sales, we get a warning that the pivot table field name already exists.

If you really want to name a value field to the same name that appears in the source data, you can add a space character to the end of the field name. Visually, the field looks the same, and Excel won’t complain about duplicate field names.

When you rename field names, you may want to confirm the original name from the source data. You can do this by accessing Field Settings for the field in question. Either right-click on the field and choose Value field settings, or click Field Settings on the Options Tab of the PivotTable Tools ribbon. Here, you can see the original field name.

In contrast to value fields, Row and Column label field names will be identical to the name in the field list. In fact, they are linked, as we’ll see in a minute.

To illustrate how this works, let’s add Category as a row label and Region as a Column label, then rename the fields.

When you add a field as a row or column label, you’ll see the same name appear in the Pivot table. You’re free to type over the name directly in the pivot table.

You can also use the Field Settings dialog box to rename the field.

When you rename fields used in Columns or Rows, the name also changes in the field list. In fact, even if you remove the field and refresh the pivot table, your new name will stick around.

To go back to the original name, re-enter the field name manually. Alternately, you can clear the pivot table and start again.

Closed 3 years ago .

• We don’t allow questions about general computing hardware and software on Stack Overflow. You can edit the question so it’s on-topic for Stack Overflow.
• Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

How does one reorder series used to create a chart in Excel?

For example, I go to the chart, right click > Select Data. In the left column I see series 1, series 2, to series n.

Say, I want to move series 3 after series 4, can it be done from chart view? I don’t want to move the data cells in the worksheet.

I’m using Excel 2011 (mac OS X).

9 Answers 9

Select a series and look in the formula bar. The last argument is the plot order of the series. You can edit this formula just like any other, right in the formula bar.

For example, select series 4, then change the 4 to a 3.

Right-click any series on the chart. In the “Format Data Series” dialog, there is a “Series Order” tab, in which you can move series up and down. I find this much easier than fiddling with the last argument of the series formula.

This is in Excel 2003 in Windows. There is a similar dialog in Excel 2011 for Mac:

These are the buttons UP/DOWN

Use the below code, If you are using excel 2007 or 2010 and want to reorder the legends only. Make sure mChartName matched with your chart name.

Excel 2010 – if you’re looking to reorder the series on a pivot chart:

• go to your underlying pivot table
• right-click on one of the Column Labels for the series you’re looking to adjust (Note: you need to click on one of the series headings (i.e. ‘Saturday’ or ‘Sunday’ in the example shown below) not the ‘Column Labels’ text itself)
• in the pop-up menu, hover over ‘Move’ and then select an option from the resulting sub-menu to reposition the series variable.
• your pivot chart will update itself accordingly

FYI, if you are using two y-axis, the order numbers will only make a difference within the set of series of that y-axis. I believe secondary -y-axis by default are on top of the primary. If you want the series in the primary axis to be on top, you’ll need to make it secondary instead.

To change the stacking order for series in charts under Excel for Mac 2011:

1. select the chart,
2. select the series (easiest under Ribbon>Chart Layout>Current Selection),
3. click Chart Layout>Format Selection or Menu>Format>Data Series …,
4. on popup menu Format Data Series click Order, then click individual series and click Move Up or Move Down buttons to adjust the stacking order on the Axis for the subject series. This changes the order for the plot and for the legend, but may not change the order number in the Series formula.

I had a three series plot on the secondary axis, and the series I wanted on top was stuck on the bottom in defiance of the Move Up and Move Down buttons. It happened to be formatted as markers only. I inserted a line, and presto(!), I could change its order in the plot. Later I could remove the line and sometimes it could still be ordered, but sometimes not.

In this post, we will change the name of the existing solution and project to a new name. This is helpful when you have cloned some repository code and want to rename it to a suitable name.

Prerequisites

1. Visual Studio

Download link is available in Downloads page.

Rename Solution and Project

I have an existing code opened in Visual Studio. The existing name is WebApplication3 . I want to change this name on both solution and project to MyWebApp .

Right click on the solution and click on Rename. Similarly, do it for the project as well.

Sometimes, after you rename, there will be a yellow warning marker on the Dependencies. It is absolutely fine. If you see the marker, just restart the visual studio and open the project again. It will go away.

Now, after changing the name, it is time to change the namespace. Open any class file and check that, the namespace would still be pointing to the old project name.

To change it to new name at all the places of the whole solution, click on Cntrl+F . Mention the old name and click on the dropdown button.

Enter the new name in the second input box. Make sure to check Match Case , Match Whole Word , select Entire Solution from the dropdown and finally click on Replace All .

With this, all your references are now changed to a new name in whole solution.

In this video, we’ll take a closer look at data series.

When you create a chart in Excel, you’re plotting numeric data organized into one or more “data series”.

A data series is just a fancy name for a collection of related numbers in the same row, or the same column.

For example, this data shows yearly sales of shorts, sandals, t-shirts, and hoodies for an online surf shop.

If I create a column chart with the default options, we get a chart with three data series, one for each year.

In this chart, data series come from columns, and each column contains 4 values, one for each product.

Notice that Excel has used the column headers to name each data series, and that these names correspond to items you see listed in the legend.

You can verify and edit data series at any time by right-clicking and choosing Select Data.

In the Select Data Source window, data series are listed on the left. If I edit one of the entries, you can see that the data series has both a name and range of values.

You’re free to edit this information manually.

So what are the values listed on the right side of Select Data Source window?

These are axis labels, in this case, Horizontal axis labels, as you can see on the chart.

In short, this chart pulls data series names from columns, and axis labels from rows.

If I click the Switch Row/Column button, this is reversed.

The data series now come from rows and axis labels come from columns.

Again, notice the legend lists data series names.

Finally, I want to point out that when you select a data series, you’ll see formula in the formula bar. This formula is based on the SERIES function, which takes four arguments:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

As I select each series, you can see these arguments change to match the data highlighted on the worksheet.

You can edit the SERIES formula if you like. For example, if I change plot order for the shorts data series to 4, Excel automatically plots the series last, and adjusts the order of the other series automatically.

By Madhuri Thakur

Data Table in Excel (Table of Contents)

Data Table in Excel

Data tables are used to analyze the changes seen in your final result when certain variables are changed from your function or formula. Data tables are one of the existing parts of What-If analysis tools, which allow you to observe your result by experimenting it with different values of variables and to compare the outcomes stored by the data table.

Excel functions, formula, charts, formatting creating excel dashboard & others

There are two types of a data table, which are as follows:

1. One-Variable Data Table.
2. Two-Variable Data Table.

How to Create Data Table in Excel?

Data Table in Excel is very simple and easy to create. Let’s understand the working of the Data Table in Excel by Some Examples.

Data Table in Excel Example #1 – One-Variable Data Table

One-variable data tables are efficient in the case of analyzing the changes in the result of your formula when you change the values for a single input variable.

Use case of One-Variable Data Table in Excel:

The one-variable data table is useful in scenarios where a person can observe how different interest rates change the amount of their mortgage amount to be paid. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.

The table above shows the data where the mortgage amount is calculated based on the interest rate, mortgage period and loan amount. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3).

In the case of observing the monthly mortgage amount for different interest rates, where the interest rate is considered as a variable. In order to do this, there is a need for creating a one-variable data table. The steps to create the one-variable data table are as follows:

Step 1: Prepare a column which consists of different values for the interest rates. We have entered different values for interest rates in the column which is highlighted in the figure.

Step 2: In the cell (F2), which is one row above and diagonal to the column which you prepared in the previous step, type this = C6.

Step 3: Select the entire prepared column by values of different interest rates along with the cell where you had inserted the value, i.e. F2 cell.

Step 4: Click on the ‘Data’ tab and select ‘What-If Analysis’, and from the options popped down, select ‘Data Table’.

Step 5: Data table dialog box will appear.

Step 6: In the Column input cell, refer to cell C4 and click OK.

In the dialog box, we refer to the cell C4 in the Column input cell and keep the row input cell empty as we are preparing a data table with one variable.

Step 7: After following all the steps, we get all the different mortgage amounts for all entered interests rates in column E (unmarked), and the different mortgage amounts are observed in column F (marked).

Data Table in Excel Example #2 – Two-Variable Data Table

Two-variable data tables are useful in scenarios where a user needs to observe the changes in the result of their formula when they change two input variables simultaneously.

Use-case of Two-Variable Data Table in Excel:

The two-variable data table is useful in scenarios where a person can observe how different interest rates and loan amounts change the amount of their mortgage amount to be paid. Instead of calculating for individual values separately, we can observe them with instantaneous results. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.

The above example is similar to our example shown in the previous case for a one-variable data table. Here the mortgage amount in cell C6 is calculated based on the interest rate, mortgage period and loan amount. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3).

In order to explain the two-variable data table with reference to the above example, we will show the different mortgage amounts and choose the best which suits you by observing the different values of interest rates and loan amount. In order to do this, there is a need for creating a two-variable data table. The steps to create the one-variable data table are as follows:

Step 1: Prepare a column which consists of different values for the interest rates and loan amount.

STEP 1: Select the Classes Table. Go to Insert > Pivot Table > New Worksheet

Make sure to tick Add this data to the Data Model. Click OK.

STEP 2: Select the Students Table. Go to Insert > Pivot Table > New Worksheet

Make sure to tick Add this data to the Data Model. Click OK.

STEP 3: Click All in PivotTable Fields and you should see both tables there.

STEP 4: Now we need to link them together! Go to PivotTable Tools > Analyze > Calculations > Relationships

Click New.

STEP 5: There are 2 sides of a relationship when we want to link them together.

The rule of thumb, is the primary table should have no duplicates. This is the Students table as it does not have duplicate Student Ids.

Set the following then Click OK.

Table – Classes

Column – StudentId

Related Table – Students

Related Column – StudentId

Click Close.

STEP 6: In the ROWS section put in the Students(FirstName) field. In the VALUES section put in the Classes (ClassName) field.

With just that, you can see that Excel was able to show the results in a merged fashion! You can see Daisy has 2 classes enrolled. And from the individual tables, you would not have that information readily available!

How to Use the Data Model in Excel Pivot Tables

Account Information

Share with Your Friends

How to work with multiple worksheets in a Microsoft Excel workbook

How to work with multiple worksheets in a Microsoft Excel workbook

Learn how to take advantage of Excel’s ability to link and sync multiple worksheets in the same workbook.

Image: Thanadol Benyasirisataporn, Getty Images/iStockphoto

If most of your Excel workbooks contain only a single worksheet, you’re missing out on the power and flexibility of using multiple sheets in a single workbook. By storing multiple Excel worksheets in the same workbook, you can tie them all together to save time and effort. You can perform the same data and formatting changes on all your worksheets in one fell swoop. You can create formulas in one worksheet that reference data in another sheet. And if any data changes in one worksheet, it also changes in any linked worksheets

What’s hot at TechRepublic

• Why Microsoft Lists is the new Excel
• Best employee monitoring software 2022
• 2022 tech conferences and events to add to your calendar
• Google, Apple, Microsoft promise end to passwords, courtesy of your mobile phone

In this tutorial, I’m using the latest flavor of Excel courtesy of Office 365, but the information offered will work similarly in the past couple of editions of Excel. To illustrate the examples, I created a workbook with five different sheets for product sales. The first four Excel worksheets display results from different regions: North, South, East, and West. The fifth worksheet is for totals from each of the other sheets. Each sheet has columns for the first six months of the year and rows for four different products.

The first thing we’re going to do is apply formatting to key rows and columns in the five sheets. But instead of formatting each sheet individually, we’re going to select them all and format them in one shot.

To select more than one sheet, we’re going to use the familiar Ctrl or Shift maneuver. You can be in any sheet for this to work. To select non-contiguous sheets, such as the first sheet, the second sheet, and the fourth sheet, hold down the Ctrl key and click on the tab for the first sheet, then the second sheet, and then the fourth sheet. To select multiple contiguous sheets, hold down the Shift key, click on the tab of the first sheet you want to include, and then click on the tab of the last sheet. In this case, we want to select all five sheets in the workbook. So hold down the Shift key, select the tab of the first sheet, and then select the tab of the last sheet. The sheets are now all selected (Figure A).

Figure A

Let’s change the formatting. Select the active cells in the top row. Apply any formatting of your choice, such as a specific style or a combination of attributes (font, size, bold, italics, color, etc.). Do the same with the cells in the first column that contain the product names (Figure B).

Figure B

Click on one of the selected tabs to turn off the selection of them all. Click on each of the tabs, and you’ll see that the formatting you applied appears on each sheet (Figure C).

Figure C

We want to total each of the columns. For this, hold down the Shift key. Select the tab for the first sheet and the tab for the fourth sheet. Type the word Totals in the blank cell at the bottom of the first column (Figure D).

Figure D

Move your cursor to the blank cell at the bottom of the second column. Click on the AutoSum icon on the Home Ribbon and make sure the four cells above are referenced in the formula. Press enter to apply the formula (Figure E).

Figure E

Move your cursor to the lower right corner of the cell with the formula. Drag your cursor to the next four cells to copy the formula to each one (Figure F).

Figure F

To turn off the selection, hold down the Shift key and click on the tab for the first sheet. Click on each of the sheets to see that the totals have been applied to all of them (Figure G).

Figure G

We’re going to fill the cells in the Totals sheet with the totals from each corresponding cell in the other sheets. To do this, move to the Totals sheet and put your cursor in the first blank cell in the second column. Type an = sign to start a formula. Click on the tab of the first sheet and then click on Cell B2. Notice that the formula says: =North!B2. Click in the Formula bar and type a + sign to continue the formula. Click the second sheet and select B2. Type another + in the formula bar. Continue this process until the formula reads: =North!B2+South!B2+East!B2+West!B2. Press Enter and the formula and total should appear in the Totals sheet in Cell B2 (Figure H).

Figure H

In the Totals sheet, move your cursor to the lower right corner of the cell with the formula. Drag your cursor to the next four cells to copy the formula to each one. Then drag the entire top row of the cells with the formulae to the four rows below to apply the totals to each cell (Figure I).

Figure I

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.

There are several instances when we need to make a comma separated text values from an excel column. There are different but pretty lengthy process that you can find, which will eat a no. of your valuable time to process the same.

So, what to do? Today we are going to learn a quick and easiest way to copy a column of data to a comma separated list. Continue reading to know more.

Who wants to give his effort to complete a hard/difficult job? Let’s for example, if you have an Excel sheet having a column of huge data and you want to put them in a comma separated list, what will you do? You may think to manually put a comma (‘,’) after each cell and move up the next cell of the column! Or, if you know little bit of excel functions, you may think to try that! If you are a dumb, you just pass on to your junior. Isn’t it?

At that point, do you ever think that it could be possible by an expert in few seconds/minutes and will utilize a no. of free times showcasing that the work is in-progress?

If you don’t want to do that and want the job done by yourself, here’s the easy steps:

Open your excel sheet and select the cells of the column that you want to process. Select only one column. Copy the selected cells.

Now open word document, right click on it and from the context menu, select ‘Paste Options‘ as ‘Keep texts only‘. This will put the selected column in the word document but in plain text format.

Now press ‘CTRL + H‘ to open the ‘Find and Replace‘ dialog. As shown in the below screenshot, enter “^p” in the ‘Find what‘ field and enter “,” (comma) in the ‘Replace with‘ field. Now click on the ‘Replace All‘ button.

Within a few moment, you will see that the cell values of the column has been switched to a single line having ‘,’ (comma) as the separator.

Done? Wasn’t it easy enough to convert a column of huge excel data to a line of comma separated values? How much time it takes for you to achive this? If you know a better solution, do let us know. We will love to hear that. Have a great day ahead!

In this lesson, we’ll look at how to name and rename worksheets. We’ll also look at how to change the color of worksheet tabs.

Let’s take a look.

When you open a new workbook, or when you add new worksheets to an existing workbook, Excel uses a generic name for each sheet: Sheet1, Sheet2, Sheet3, and so on.

As you build out a workbook to meet your needs, you’ll probably want to rename these sheets to keep things organized.

The easiest way to rename a worksheet is to double-click its name. This will highlight the text of the name, and then you can type a new name.

Press enter to confirm the change.

You can also right-click on a worksheet and choose Rename from the menu.

As before, Excel will highlight the text and you can type a new name.

Once text is highlighted, you can click to move the cursor inside the text and edit as you like. You can also double-click to select individual words.

There are a few rules to keep in mind when you rename tabs.

Excel worksheet names need to be at least one character long, and they can’t be longer than 31 characters.

You can’t have two worksheets with the same name in the same workbook.

Certain characters are not allowed in worksheet names, including square brackets, asterisks, question marks, forward and backward slashes, periods, apostrophes, and colons. If you try to type these characters into a worksheet name, Excel will simply ignore the input.

In addition to changing a worksheet’s name, you can also change the color of a worksheet tab. To change the color, just right-click and choose Tab Color from the menu. You can then choose a color of your choice.

Note that you won’t see the color change until you click enter and select another worksheet.

The truth is: before you go for a job interview, you must have basic knowledge of Microsoft Excel. From an accountant to a receptionist, human resources to administration departments all are using Microsoft Excel.

It is not only limited to the large companies, small entrepreneurs and college students are using it for their day-to-day work. That’s something which you can’t skip. To get a job, learning basic Excel tasks (at least some) is a must in today’s era, that’s a firm truth.

And, to help you in this I have compiled this guide. This guide will help you to learn all those basics using some examples. And some of the most important beginner’s tutorials.

So without further ado let’s get down to the business.

Introduction to Microsoft Excel

There is a number of spreadsheet programs but of all of them, Excel is the most widely used. People have been using it for the last 30 years and throughout these years, it has been upgraded with more and more features.

The best part about Excel is, that it can apply to many business tasks, including statistics, finance, data management, forecasting, analysis, tracking inventory & billing, and business intelligence.

Following are the few things which it can do for you:

• Number Crunching
• Charts and Graphs
• Store and Import Data
• Manipulating Text
• Templates/Dashboards
• Automation of Tasks
• And Much More…

The three most important components of Excel you need to understand first:

1. Cell

A cell is a smallest but most powerful part of a spreadsheet. You can enter your data into a cell either by typing or by copy-paste. Data can be a text, a number, or a date. You can also customize it by changing its size, font color, background color, borders, etc.

Every cell is identified by its cell address, cell address contains its column number and row number (If a cell is on the 11th row and on column AB, then its address will be AB11).

2. Worksheet:

A worksheet is made up of individual cells which can contain a value, a formula, or text. It also has an invisible draw layer, which holds charts, images, and diagrams. Each worksheet in a workbook is accessible by clicking the tab at the bottom of the workbook window.

In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is accessible by clicking a tab.

3. Workbook

A workbook is a separate file just like every other application has. Each workbook contains one or more worksheets. You can also say that a workbook is a collection of multiple worksheets or can be a single worksheet.

You can add or delete worksheets, hide them within the workbook without deleting them, and change the order of your worksheets within the workbook.

Microsoft Excel Window Components

Before you start using it, it’s really important to understand what’s where in its window. So ahead we have all the major components which you need to know before entering the world of Microsoft Excel.

1. Active Cell: A cell that is currently selected. It will be highlighted by a rectangular box and its address will be shown in the address bar. You can activate a cell by clicking on it or by using your arrow buttons. To edit a cell, you double-click on it or use F2 as well.
2. Columns: A column is a vertical set of cells. A single worksheet contains 16384 total columns. Every column has its own alphabet for identity, from A to XFD. You can select a column by clicking on its header.
3. Rows: A row is a horizontal set of cells. A single worksheet contains 1048576 total rows. Every row has its own number for identity, starting from 1 to 1048576. You can select a row by clicking on the row number marked on the left side of the window.
4. Fill Handle: It’s a small dot present on the lower right corner of the active cell. It helps you to fill numeric values, text series, insert ranges, insert serial numbers, etc.
5. Address Bar: It shows the address of the active cell. If you have selected more than one cell, then it will show the address of the first cell in the range.
6. Formula Bar: The formula bar is an input bar, below the ribbon. It shows the content of the active cell and you can also use it to enter a formula in a cell.
7. Title Bar: The title bar will show the name of your workbook, followed by the application name (“Microsoft Excel”).
8. File Menu: The file menu is a simple menu like all other applications. It contains options like (Save, Save As, Open, New, Print, Excel Options, Share, etc).
9. Quick Access Toolbar: A toolbar to quickly access the options which you frequently use. You can add your favorite options by adding new options to the quick access toolbar.
10. Ribbon Tab: Starting from Microsoft Excel 2007, all the options menus are replaced with ribbons. Ribbon tabs are a bunch of specific option group which further contains the option.
11. Worksheet Tab: This tab shows all the worksheets which are present in the workbook. By default you will see, three worksheets in your new workbook with the name of Sheet1, Sheet2, and Sheet3 respectively.
12. Status Bar: It is a thin bar at the bottom of the Excel window. It will give you instant help once you start working in Excel.

Microsoft Excel Basic Functions

Excel Functions are one of the most important features of Excel. It helps you to perform the basic calculations as well as complex. Below I have listed 10 Basic Excel Functions that you need to learn.

Microsoft Excel Basic Tutorials

Below I have listed some of the most important Basic Microsoft Excel tutorials which can be helpful for you in day-to-day work.

Recommended Books

Below are my two favorite Excel books for beginners which every person who is starting out with Excel should read.

1. Excel 2016 for Dummies: This book covers everything you need to know to perform the task at hand. Includes information on creating and editing worksheets, formatting cells, and entering formulas […]
2. Microsoft Excel 2016 Bible: Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs Whether you use Excel at work or […]

If a table has more than one series, Excel charts it in the order it appears on your worksheet (from left to right if your series are arranged in columns, or from top to bottom if they’re arranged in rows). In a basic line chart, it doesn’t matter which series Excel charts first the end result is still the same. But in some charts, it does make a difference. One example is a stacked chart, in which Excel plots each new series on top of the previous one. Another example is a 3-D chart, where Excel plots each data series behind the previous one.

You can easily change your data series’ order. Select your chart, and then choose Chart Tools : Design > Data > Select Data. Now select one of the series in the Legend Entries (Series) list, and then click the up or down arrow buttons to move it. Excel plots the series from top to bottom.

Changing the Way Excel Plots Blank Values

When Excel creates a chart, its standard operating procedure is to ignore all empty cells. The value of 0 doesn’t count as an empty cell and neither does text (Excel plots any cells that contains text as a 0).

So what’s the difference between an ignored cell and a cell that contains the number 0? In some types of charts, there’s no difference. In a bar or pie chart, the result is the same you don’t see a bar or a pie slice for that data. However, in some charts, there is a difference. In a line chart a 0 value is plotted on the chart, but an empty cell causes a break in the line. In other words, the line stops just before the missing data, and then starts at the next point. This broken line indicates missing information.

If you don’t like this behavior (perhaps because your empty cells really do represent 0 values), you can change it. Select your chart, and then choose Chart Tools : Design > Data > Select Data to get to the Select Data Source dialog box. Then, click the Hidden and Empty Cells button, which pops open a dialog box with three choices:

Gaps
Excel leaves a gap where the information should be. In a line chart, this breaks the line (making it segmented). This option is the standard choice.

Zero
Excel treats all blank cells as though they contain the number 0.

Span with line
Excel treats all blank cells as missing information and tries to guess what should go in between. If a line chart goes from 10 to 20 with a blank cell in between, Excel interpolates the data point 15 and plots it.

You can also switch on or off the “Show data in hidden rows and columns” setting to tell Excel whether it should include cells that are hidden when creating a chart. This setting determines how Excel deals with data when you use filtering in a table, or when you explicitly hide rows or columns using the Home > Cells > Format > Hide & Unhide menu. Ordinarily, Excel treats these missing values just like blank values, and ignores them.

For data nerds, Excel’s drop-down lists are a lovely gift.

They keep entries consistent across multiple rows — no misspelled words or names written without capitalization. Drop-down lists are essential if you need to sort your data or create a pivot table.

For example, Excel sees “Texas” and “Tezas” as two different states, and therefore two different values, but a drop-down list with the names of the states can prevent errors like this.

Check out the products mentioned in this article:

Microsoft Office (From $129.99 at Best Buy)

Lenovo IdeaPad 130 (From $299.99 at Best Buy)

MacBook Pro (From $1,299.99 at Best Buy)

How to create a drop-down list in Excel

1. The first step is to create a list with all the items you want in your drop-down list.

• You can create your list on the same sheet where you will be entering data from the drop-down list.
• Putting the list entries on the same tab can create confusion. The best practice is to create a separate worksheet for your drop-down list. To create a new tab, click the “+” icon next to the last tab in your spreadsheet. Double click the tab to rename it.
• You’ll also want to make sure your items are in a table. If they aren’t, you can convert your list to a table by holding “Ctrl” + “T” on your PC or “command” + “T” on your Mac keyboard.

2. In your sheet, select the cells where you want the drop-down list to appear. You can also select a whole column.

3. Click on the “Data” tab in the top menu so the Data menu ribbon appears.

4. Click on the arrow next to “Data Validation.”

5. Choose “Data Validation” from the drop-down menu.

6. In the “Settings” tab in the top menu, under “Allow,” click “List.”

7. Click in the Source box, and the window will shrink to show only this field.

8. Highlight the cells that contain your list. If you put your list in a separate tab, you can open that tab to highlight the cells. The cell range will appear in the window. Hit enter or “return” on your keyboard to set the range for your list.

9. The larger window will reappear. Click “OK” to set your drop-down list.

10. You can see if a cell has a drop-down list because an arrow will appear next to the cell. When you click on the arrow, the list appears.

11. Enter your data, using the drop-down list to supply values.

If you need to edit your drop-down list, select the cells where the list appears and choose “Data Validation.” The details of your list will appear. You can click on “Clear All” to remove the list or change the source range to add or subtract items from your drop-down list.

If you are looking for Excel based Data Entry form with multi-users functionalities then this post is going to be very helpful for you. In this post, we will discuss User Interface (UI) design and VBA coding to develop the Multi-user Data Entry Form.

Why do we need to develop Multi-user Data Entry Form in Excel?

Microsoft Excel is one of the most utilized tool in any company. Due to availability and dynamic features of Excel, we can use this tool to automate any complex data entry operation. There is no need to hire programmers and install a separate language. MS Excel has an inbuilt integrated development environment (IDE). Visual Basic for Applications (VBA) is the programming language for Microsoft Office and its associated applications e.g. Excel, Word, Power Point etc. We can leverage the power of VBA language and develop the multi-user data entry form without any additional cost.

In this Multi-User Data Entry form, we have two separate Excel files. One is fulfilling the purpose of Database and other is for Data Entry Form to transfer the data to database workbook.

To use this tool, you just need to keep the parent folder at any shared location and ask your team and users to open the Data Entry Form and start submitting the data. Excel VBA code will handle all the required functionalities e.g. accessing database, validating entry, transferring inputs etc.

Creating Multi-user Data Entry Form in Excel

Follow the below steps to create this Automated Data Entry form in Excel.

1. Create a folder named ‘Multi-user Data Entry Form’ at the shared drive.

Create Folder

2. Create a new Workbook and save the file with the name ‘Database.xlsm” in ‘Multi-user Data Entry Form folder’.

Create Database File

3. Rename the ‘Sheet1’ to ‘Database’ and add the below mentioned column headers and format the the headers accordingly in ‘Database.xlsm’ file.

Database Headers

4. Create a new Workbook and save the file with the name ‘Multiuser Data Entry Form in Excel.xlsm’ in ‘Database’ folder.

5. Rename the ‘Sheet1’ to ‘Home’. Remove the Gridlines ( View – Gridlines) and add a rounded rectangle and fill the color with Blue and enter the text ‘Data Entry Form’. Please see the below image.

Data Entry Form Button

6. Now go to Develop Tab and click on Visual Basic to visit Visual Basic Application window.

Develop Tab

7. In Visual Basic for Application window, click on Insert menu and then select ‘UserForm’.

Add UserForm

8. Set the properties of the userform and add required controls. Add label, texboxes, image control, option button, combobox and command button as per below image.

Labels: Employee Registration Form (Top headers), Name, Date of Birth, Gender, Qualification, Mobile Number, Email ID, Address

TextBox: Name – txtName; Date of Birth – txtDOB; Mobile Number – txtMobile; Email-txtEmail; Address:txtAddress

Image: Calendar- imgCalendar

Option Buttons: Gender – Female – optFemale; Male-optMale

Combobox: Qualification- cmbQualification

Command Buttons: Submit- cmdSubmit; Reset-cmdReset

Multi-User Data Entry Form

9. Set the Tab order for each and every cotnrols.

10. Import the custom calendar from the support file. Link has been provided at the bottom of the page.

11. Insert a module and rename it to ‘mdDataEntry’.

Add Module

12. In Module code window, start writing the code to handle Reset, Validate Email, Validate Fields, Transfer Data and Call Form.