If you're like me, you love the idea of graphics and can think of dozens of useful applications for them, but when it comes to creating them the frustration begins.
Charts often make decision making easier. They are a nice change from looking at rows and columns of numbers. They add a bit of flair and pizazz. 6 New Excel charts and how to use them. 6 New Excel charts and how to use them. Need to get more out of your Excel charts? Here's a complete guide to some of the new charts introduced in the latest version of Excel. Read More However, creating complex charts in Microsoft Excel can be intimidating, so it's easy to end up settling for simple charts that take a lot of work to maintain.
Charts help shorten the decision-making process as we can immediately see our results and where we need to make changes. The difficulty in handling data and graphs is that you constantly have to go back to the chart and update it for new data.
Well, no more! I'll show you three easy steps to create charts in Microsoft Excel that update automatically. All you have to do is add data to the spreadsheet, and the chart will graph it automatically. You won't have to rely on others to manipulate or clutter the table, and you won't have to do all that extra work either. You don't need any Visual Basic skills, but you do need to understand the basics of Microsoft Excel charting.
To create an auto-update chart, we first need to set up a spreadsheet. 10 Incredibly Useful Spreadsheet Templates to Organize Your Life. 10 Incredibly Useful Spreadsheet Templates to Organize Your Life. Your life is a hotbed of missed dates, forgotten purchases, and reneged commitments. ? Sounds like you need to get organized. Read More The format of this document is important because we need to be able to add more data on an ongoing basis.
Lay out your information and make sure each column has a heading. For my example project, I'm creating a table that tracks the sales of each Harry Potter novel in a bookstore.
The kind of chart we're making works best if you can extend the new entries to new rows below. In my example, as new sales data is recorded, I would add it to the spreadsheet starting at row 11.
Lay out your information, check that each column has a reasonable heading, and you're ready for the next step.
In Office 2016, tables have advanced 8 Formatting Tips for Perfect Tables in Microsoft Word 8 Formatting Tips for Perfect Tables in Microsoft Word Tables in Microsoft Word are essential formatting tools. Find out how to use them to their fullest potential with these simple tips. Read more . Instead of making your data look neat and tidy, they help you group information together. The ultimate goal here is to create a table that feeds the data into a graph. Linking these two elements allows the chart to verify the newly added data in the table.
To create a table, select all the data you want to turn into a chart. Then go to the Insert tab and select Table - alternatively, you can use the shortcut CTRL + T .
In the Create table In the dialog box, you can adjust the cells included in the table. Check the box labeled My table has headers , then press OK .
Your data should undergo the following appearance change:
With that change made, we're ready to work on our table.
Select the entire table and go to Insert> Letters To choose which type of display to use. The correct chart will depend on the type of data you are working with. For my example I am using a line chart. This allows me to compare multiple columns of different data on a single chart, and it works great with automated updates.
Now is a good time to make any format changes. 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions are important. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know to make your charts beautiful and engaging in Excel 2016. Learn more or visual tweaks. Once this is done, we can test adding new data to the chart. Next, we need to check if our auto-update box works. Fortunately, this is the easiest part of the process.
To add more data, simply add another line to the bottom of your existing chart. Since the Date column indicates the values on the X-axis of my chart, I'll start there. Date formatting can be a bit tricky, so I'll drag the bottom right corner of the cell down to automatically fill the cell. Of course I could do this manually, I would just have to put my date in the same format as the previous rows.
You may see a dialog warning you that the table inserted rows into the worksheet; this is absolutely correct. Your chart should have already updated to include the new entry on its x-axis. Assuming that's correct, it's safe to start adding new data to the chart.
Above, you can see that I added a sales count of 10 for each book to prompt the table to be updated. You can now use the graph indefinitely, simply by adding more rows to the table. However, you may need to adjust its size and format to present all the data correctly. Automatic formatting of data in Excel spreadsheets with conditional formatting Automatic formatting of data in Excel spreadsheets with conditional formatting Excel's conditional formatting feature allows you to format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various daily tasks. Read More
One of the most powerful aspects of Microsoft Excel is the fact that various aspects of spreadsheets can be automated. This could be something as simple as creating a basic auto-update box, or something more complex, like diving into Visual Basic. The Excel VBA Tutorial for Beginners. The Excel VBA Tutorial for Beginners. VBA is a powerful tool in Microsoft Office. You can use it to automate tasks with macros, set triggers, and much more. We will introduce you to basic Excel visual programming with a simple project. Read more.
By putting in a little effort up front, you can save a lot of time later on. Challenge yourself to learn something new in Microsoft Excel, and it will pay off tenfold in the long run.
Need help setting up your table and linking it to a chart? Or have you found a particularly powerful use for a self-updating box like this? Whether asking for help or offering it, why not join the conversation in the comments section below?
Originally written by Paul Coffman in October 2009..
