How many types of elements are there in Excel?

Leer en español
Ler em português

The past 10 years have seen a wave of innovative big data software designed to analyze, manipulate, and visualize data. Yet for the regular knowledge worker, Microsoft Excel, 30 years on, remains the go-to product for people looking to make sense of data. Satya Nadella, Microsoft’s CEO, maintains that Excel is still the one Microsoft product that stands above the rest — and 750 million knowledge workers worldwide support that claim every day.

We’ve been teaching and testing Microsoft Excel for a decade, and a survey of several hundred office staff we ran suggests we spend more than 10% of our working lives spreadsheeting; for those working in research and development or finance, it’s more like 30%, or 2.5 hours a day.

Imagine, then, if this substantial proportion of the global workforce were a little better at using the application. Time would be saved, and productivity would improve.

Help your employees be more data-savvy.

Last year we put together “The Definitive 100 Most Useful Excel Tips,” for which we consulted Excel experts and looked at tens of thousands of test results and course usage data. Though every Excel feature has a use case, no single person uses every Excel feature. Cut through the 500+ functions, and you’re left with 100 or so truly useful functions and features for the majority of modern knowledge workers. We’ve selected 10 from that list that are especially easy to learn (approximately two hours total) and can make a material difference to productivity. These 10 are listed in decreasing order of utility from our top 100 list. If, like many, you’re stuck on what to learn next in Excel, you might want to look at this 2×2 matrix, which factors in usefulness and time needed to learn a skill.

Copy and paste is one of the simplest and most used functions in Excel. But we often carry over a format we don’t want, or we copy a formula over when instead we just want a value.

These little frustrations can take time to fix, which is why Paste Special is so…special. Paste Special enables you to pick which elements of the copied cell you bring over. After you’ve copied your cell (Ctrl+C), hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values — probably the most common use of Paste Special.

We often need to add new rows between existing rows. The shortcut (Ctrl+Shift++ [plus sign]) is pretty handy, especially as you can toggle the + to add multiple rows. Often, just highlighting the number of rows you want to add (say five) and using right click+insert is quicker when adding in bulk as it will add the number of rows you’ve highlighted.

Excel developed a mind of its own in 2013 with this feature. Flash Fill automatically fills your data when it senses a pattern.

Suppose you have a list of product numbers in the first 10 cells of column A, for example “ABC-00001” to “ABC-00010” and you only need the numbers after the “-”. You can easily discard the “ABC” using Flash Fill. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, and so forth). Now, this is much faster and will impress people.

Establish the pattern by typing “00001” into the first blank cell. If Flash Fill is turned on (File Options, Advanced), just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Just hit the Enter key to accept. Or get it going manually by clicking Data > Flash Fill, or Ctrl+E.

Flash Fill is like magic, and can be used in many different scenarios. It’s a fantastic time-saver when you need to input or change a lot of data quickly and accurately.

Aside from VLOOKUP (which looks up the value in one column and returns a corresponding value from another column), INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. Used separately, these functions are invaluable, but when you combine them, their true power is unleashed. INDEX and MATCH used in combination helps you extract the data you need from a large data set efficiently and precisely. Mastering these functions will not only make you look like an Excel whiz to your colleagues and manager — it will also make tedious, mundane tasks quick and simple. Here’s how these functions work:

VLOOKUP is a great function, but it has its limitations. It can look up values only from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH allows you to look up a value anywhere in the lookup table regardless of its position.

Let’s say you have a spreadsheet with a list of products. You have columns titled “Product Number,” “Profit,” “Product Name,” and “Revenue.” On another spreadsheet, you have a list of the product names, and you want to look up how much profit each product has generated. In this scenario, we are using the product name (our lookup value) to look up the profit. The product name sits to the right of the profit, so VLOOKUP would not work. This is the perfect scenario for INDEX and MATCH.

The syntax would be:

=INDEX(Profit column),MATCH(Lookup Value,Product Name column),0

Here’s a good way to remember how it works:

=INDEX (column I want a return value from), MATCH (my lookup value, column I want to look up against), Enter “0” (Zero gives you an exact match; you can match against less than [-1] or greater than [1] as well.)

At first and even second glance, INDEX and MATCH looks complex. It certainly needs some practice, but it’s worth getting your head around as it is more flexible and more powerful than a VLOOKUP. It’s one of the most important uses of Excel, period.

This is one of the first functions you’re likely to learn in Excel — how to sum a row or column. But did you know you can select the cell at the end of a row or column and press Alt++ [plus sign] to do this function in seconds?

This is your Excel get-out-of-jail-free card. If you aren’t using Ctrl+Z to undo mistakes in Excel, then you should be. What many people don’t know is that Ctrl+Y does the opposite — redo. The two work in tandem very nicely, and you can cycle through iterations of your work until you find the right one.

This is so simple and quick to use. Remove Duplicates does exactly what you’d expect — it removes the duplicates in any given range of data. Our advice is to remove the values you want to dedupe and place them in another sheet. It’s found on the Data tab in the Data Tools section of the ribbon.

If you just want to highlight duplicates, you can do this using Conditional Formatting. The shortcut to get you there is Alt+H+L (or find it on the Home ribbon under Styles).

Ever scroll down a large table of data only to forget which columns are which? Freeze Panes is the answer here. You can freeze just the top row, first column, or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt+W+F is the shortcut.

There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. For example, if you’ve just applied a border to one cell, use F4 to apply it to others.

If you’ve found yourself scrolling through a data set to reach the bottom of it, stop right now and start using Ctrl+the arrow keys! This simple shortcut takes you straight to the end of the data in a column or row you are using. Combine it with Ctrl+Shift to highlight/select large areas of data in seconds.

Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data that has no gaps.

Digital transformation isn’t slowing down. Make sure your organization doesn’t either.

. . .

Harness just a few of these 10 items, and you can transform your typical workday. Whether you want to help justify data-driven business decisions at a high level or simply get home to your family earlier, mastering the right Excel functions is a quick and easy way to maximize your productivity.

Chart elements give more descriptions to your charts, thus making your data more meaningful and visually appealing. In this chapter, you will learn about the chart elements.

Follow the steps given below to insert the chart elements in your graph.

Step 1 − Click the chart. Three buttons appear at the upper-right corner of the chart. They are −

• 
  Chart Elements
• 
  Chart Styles and Colors, and
• 
  Chart Filters

Step 2 − Click the

The following chart elements are available −

• Axes
• Axis titles
• Chart titles
• Data labels
• Data table
• Error bars
• Gridlines
• Legend
• Trendline

You can add, remove or change these chart elements.

Step 3 − Point on each of these chart elements to see a preview of how they are displayed. For example, select Axis Titles. The Axis Titles of both, the horizontal and the vertical axes appear and are highlighted.

A

Step 4 − Click

Step 5 − Select/deselect the chart elements, which you want in your chart to be displayed, from the list.

In this chapter, you will understand the different chart elements and their usage.

Axes

Charts typically have two axes that are used to measure and categorize the data −

• A vertical axis (also known as value axis or y axis), and
• A horizontal axis (also known as category axis or x axis)

3-D Column charts have a third axis, the depth axis (also known as the series axis or the z axis), so that the data can be plotted along the depth of a chart.

Radar charts do not have horizontal (Category) axes. Pie and Doughnut charts do not have any axes.

Not all chart types display axes the same way.

• x y (Scatter) charts and Bubble charts show numeric values on both the horizontal axis and the vertical axes.

• Column, Line, and Area charts, show numeric values on the vertical (value) axis only and show textual groupings (or categories) on the horizontal axis. The depth (series) axis is another form of category axis.

Axis Titles

Axis titles give the understanding of the data of what the chart is all about.

• You can add axis titles to any horizontal, vertical, or the depth axes in the chart.

• You cannot add axis titles to charts that do not have axes (Pie or Doughnut charts).

To add Axis Titles,

Step 1 − Click on the chart.

Step 2 − Click the

Step 3 − From the list, select Axes Titles. Axes titles appear for horizontal, vertical and depth axes.

Step 4 − Click the Axis Title on the chart and modify the axes titles to give meaningful names to the data they represent.

You can link the axes titles to the cells containing text on the worksheet. When the text on the worksheet changes, the axes titles also change accordingly.

Step 1 − On the chart, click any axis title box.

Step 2 − On the worksheet, in the formula bar, type an equal-to sign (=). Select the worksheet cell that contains the text that you want to use for the axis title. Press Enter.

The axis title changes to the text contained in the linked cell.

Chart Title

When you create a chart, a Chart Title box appears above the chart.

To add a chart title −

Step 1 − Click on the chart.

Step 2 − Click the

Step 3 − From the list, select Chart Title. A Chart Title box appears above the graph chart.

Step 4 − Select Chart Title and type the title you want.

You can link the chart title to the cells containing text on the worksheet. When the text on the worksheet changes, the chart title also changes accordingly.

To link the chart title to a cell follow the steps given below.

Step 1 − On the chart, click the chart title box.

Step 2 − On the worksheet, in the formula bar, type an equal-to sign (=). Select the worksheet cell that contains the text that you want to use as the chart title. Press Enter.

The chart title changes to the text contained in the linked cell.

When you change the text in the linked cell, the chart title will change.

Data Labels

Data labels make a chart easier to understand because they show the details about a data series or its individual data points.

Consider the Pie chart as shown in the image below.

From the chart, we understand that both the classics and the mystery contribute more percentage to the total sales. However, we cannot make out the percentage contribution of each.

Now, let us add data Labels to the Pie chart.

Step 1 − Click on the Chart.

Step 2 − Click the

Step 3 − Select Data Labels from the chart elements list. The data labels appear in each of the pie slices.

From the data labels on the chart, we can easily read that Mystery contributed to 32% and Classics contributed to 27% of the total sales.

You can change the location of the data labels within the chart, to make them more readable.

Step 4 − Click the

Step 5 − Point on each of the options to see how the data labels will be located on your chart. For example, point to data callout.

The data labels are placed outside the pie slices in a callout.

Data Table

Data Tables can be displayed in line, area, column, and bar charts. Follow the steps to insert a data table in your chart.

Step 1 − Click on the chart.

Step 2 − Click the

Step 3 − From the list, select Data Table. The data table appears below the chart. The horizontal axis is replaced by the header row of the data table.

In bar charts, the data table does not replace an axis of the chart but is aligned to the chart.

Error Bars

Error bars graphically express the potential error amounts relative to each data marker in a data series. For example, you can show 5% positive and negative potential error amounts in the results of a scientific experiment.

You can add Error bars to a data series in 2-D area, bar, column, line, x y (scatter), and bubble charts.

To add Error bars, follow the steps given below −

Step 1 − Click on the Chart.

Step 2 − Click the

Step 3 − From the list, select Error bars. Click the

Step 4 − Click More Options… from the list displayed. A small window to add series will open.

Step 5 − Select the series. Click OK.

The Error bars will appear for the selected series.

If you change the values on the worksheet associated with the data points in the series, the error bars are adjusted to reflect your changes.

For X Y (Scatter) and Bubble charts, you can display the error bars for the X values, the Y values, or both.

Gridlines

In a chart that displays the axes, to make the data easier to read, you can display the horizontal and the vertical chart gridlines.

• Gridlines extend from any horizontal and vertical axes across the plot area of the chart.

• You can also display the depth gridlines in 3-D charts.

To insert gridlines −

Step 1 − Click on the 3-D column chart.

Step 2 − Click the

Step 3 − From the list, select Error bars. Click the

Step 4 − Select Primary Major Horizontal, Primary Major Vertical and Depth Major from the list displayed.

The selected gridlines will be displayed on the chart.

You cannot display gridlines for the chart types that do not display axes, i.e., Pie charts and Doughnut charts.

Legend

When you create a chart, the Legend appears by default.

You can hide a Legend by deselecting it from the Chart Elements list.

Trendline

Trendlines are used to graphically display the trends in data and to analyze the problems of prediction. Such analysis is also called regression analysis.

By using regression analysis, you can extend a trendline in a chart beyond the actual data to predict the future values.