A Crash Course On How to Use Tables & Charts in Microsoft Excel
In our previous tutorial, we ran through the basics of navigating in Excel and how Excel stores dates and times. This article explores how you can use tables and charts to organize and visualize your Excel data in smart ways.
Creating tables and graphs is an essential part of many people's jobs. That's because these are some of the most convenient ways of viewing data in an easily digestible way. And yet, most of us are slightly intimidated by the thought of having to poke around in Excel.
This brief guide will share detailed descriptions of how you can organize and visualize your spreadsheet data into tables and charts.
Watch our latest video to learn more about how to use tables and charts in Excel:
Enough talk. Let's dive in!
What Is an Excel Table?
Tables are a special format in Excel that makes organizing and manipulating data much easier. Tables consist of a header row with titles for each column followed by any number of rows of data relating to each header content.
In Excel versions later than Excel 2007, the table command allows you to turn a list of data into a formatted Excel table. Several table features allow you to organize and visualize your data, for example, sorting, filtering, and so on.
Here are some key points to remember when working with tables in Excel:
- If you start typing in the row below a table, that row is automatically included in your table
- If you start typing in the column next to your header row, Excel adds that column to your table
- if you enter a formula in a column, Excel automatically applies that formula to the entire column
- Most importantly, if you are using a PivotTable based on this table, the data is automatically added to the PivotTable when you refresh.
- Formulas can be written using the table name and header name.
It's advisable to always convert your lists into named Excel tables as it has numerous benefits.
Why Are Excel Tables Important? Formatting a list as a named Excel table has several benefits, including:
- It's easy to sort and filter the data using the drop-down lists in the heading cells.
- When you remove or add rows of data, the table range shrinks or expands automatically.
- It's easy to change the table's appearance using the built-in styles.
- Formatting and formulas will fill down automatically.
- Tables make great sources for PivotTables as you don't need to adjust the range should you add or remove data.
How Do You Prepare Your Data? Before you can start creating a formatted table in Excel, you first have to follow these steps to organize your data:
- Arrange your data in columns and rows. Each row should contain information about one record, for example, an inventory transaction or sales order.
- Every column must have a brief, unique, and descriptive heading in the first row of your list.
- Every column in your list must contain a single data type, for example, text, dates, currency, and so on.
- Whenever possible, you should add a specific identifier, such as an order number, for every row.
- Your list must have no blank rows or columns whatsoever.
- Your list must be completely separate from any other data on your worksheet. The list should have at least one blank column and one blank row between it and any other data.
How Do You Create an Excel Table? Once you've organized everything according to the above guidelines, you can begin creating a formatted table. Here's how to go about it:
- Choose a cell in the data list you've prepared.
- Click the Insert tab on the ribbon.
- Click the table command in the tables group.
- The 'my table has headers' option will be checked, and your data range will show up automatically in the 'create table dialogue' box.
- Finally, accept these settings by clicking, OK.
How Do You Sort and Filter the Data? Once you've completed the steps above, your list will become an Excel table that's formatted automatically. However, you can change your table formatting from the default Table Style.
How Do You Create a Chart? To create a chart, follow these seven simple steps:
- The first step is keying in your data into Excel manually. Alternatively, you could export your data from somewhere else, for example, a survey tool or a piece of marketing software.
- Microsoft Excel offers a wide range of chart and graph options from which you can choose. These include column or bar graphs, pie graphs, line graphs, scatter plots, and so on. Each of these has its own unique icon in the top navigation bar.
- Highlight your data and insert your desired chart depending on the data you're working with. For example, to create a bar graph, simply highlight your data, including the x and y-axis titles. Next, navigate to the Insert tab and click the column icon in the chart section. From the drop-down window that appears, select your desired graph.
- Change the colors and layout of your data by clicking on the bar graph, followed by the chart design tab. At this point, you can select your preferred layout for the axis and chart titles as well as the legend. Click on the legend if you'd like to format it further. This should reveal the 'Format Legend' sidebar to adjust the legend's fill color, which changes the columns’ colors.
- The sizes of your legend and axis labels might be quite small if you're making a chart in Excel for the first time. However, this also depends on the type of chart or graph you select. It's, therefore, advisable to increase the sizes of your labels to make them easily visible. To do this, click on each individual label, navigate to the top navigation bar, and click back into the Home tab.
- The final step is naming your chart. Adding a title to your chart is just as fun and straightforward as it sounds. Immediately after creating your chart, something similar to 'Chart Title' will probably appear depending on your Excel version. Simply click on 'Chart Title' to reveal the typing cursor and freely customize your title. Finally, head to the top navigation bar, click on Home, and give your chosen title the necessary emphasis by using the font formatting options.
In the Market for the Most Reliable Microsoft Solutions Support in Buffalo, NY?
Our experienced IT professionals at Globalquest Solutions are eager to help you and your employees fully exploit Microsoft Excel. Reach out to us now to get started on how you can take advantage of Microsoft solutions and grow your business.
Call us now to schedule your free consultation using (877) 440-3061.