Best Excel Tips Video: Create Excel Charts, Graphs, and Tables
These insights are considered to be the best excel tips ever. Create creating stunning visuals and turning boring spreadsheets into valuable knowledge.
Hi! I’m Mike Song, author of The Hamster Revolution and the Get Control! of Excel class. And now we are going to help make your spreadsheets more informative via the smart use of stunning visuals.
Amazing Tables Tables make data easier to read. They come formatted with colored rows and filters. Place your mouse at the top right corner of your data. Click Insert > Table > Select the table area > and Click OK. At this point, you can select from a variety of table designs by clicking on the table and looking under Design for options.
Dynamic Dropdowns: Dropdown lists help users select from a consistent group of options. Create your list items in another part of your spreadsheet. Then, click on the cell in which you want your dropdown list to appear. From the Data Tab click on Data Validation in the Data Tools Area and select Data Validation again. Click on the drop-down menu below Allow: and select List.
Click on the Source area >Select the cells that contain the info that you want to appear in your dropdown list > Click OK.
Freeze Panes: If you scroll down in a spreadsheet and can’t see the headers, it can be hard to know what is actually in each column. However, if you freeze the top header row, you can always see what is in each column as you scroll down. The titles are visible! To freeze a row select the entire row below the row or rows that you want to freeze. Click the ViewTab on the ribbon and select the Freeze Panes command > Click Freeze Panes. The rows now freeze at the gray horizontal line. You can also use this process to freeze a column. To unfreeze a row or column click on View > Freeze Panes Command > Unfreeze Panes.
Smart Sort: Let’s say we want to sort this data set to see which sales rep had the biggest Total Sales number for the year. Click anywhere in the data area and select Control + A. That will select all the data in the area really fast. Now, click Data and Sort. Make sure that the box next to My data has headers is checked because our data does include headers. Now, next to Sort by, select the column that you would like to sort on. I will select Total Sales. Under Order, I will leave Largest to Smallest as my selection and click OK. And now I can clearly see that Pam had the biggest sale of the year because I was smart about sorting.
Friendly Filters: The Filter feature allows you to select what information you do and don’t see. Isolating a specific kind of data in a column provides useful information. The Filter feature is much easier to use when a worksheet contains headers or titles.
Click into any cell in the source data that you want to filter. Select Data > Filter and Filter Arrows will appear next to your headers. Click on the Filter Arrow for any column and the Filter Menu will appear. Uncheck the box next to Select All to deselect all items. Then selectively check the boxes of the items you want to see in that column and click OK. Now you are looking at a subset of your information which is incredibly useful when working on larger spreadsheets. I want to isolate sales of the Falcon2000 Drone. I simply uncheck Select All and check Falcon2000 and there you go! I could even filter two columns. For example, by clicking on the salesperson filter, I can isolate Jim’s sales of the Falcon2000.
Conditional Formatting: Conditional formatting automatically applies icons, data bars, or colors to data cells based on the value of that cell. It is one of the best excel tips ever! If you had sales info and wanted your top sales month to be in Green and your worst sales month to be red, you can do it with Conditional Formatting.
Select the cells where you want to apply conditional formatting. From the Home Tab, click on Conditional Formatting > Hover your mouse over the preferred conditional formatting type. There are a ton of options! Click on your format type and presto – now you can see patterns and highlights in the data. For example, it looks like Carlos, our number one sales rep started the year with excellent sales—lots of green — but is trending downwards in Q2 as signified by the red color here.
Chill Charts Charts: They provide a colorful, informative representation of data contained within spreadsheets. Select the data in a worksheet that you want to use for a chart. Include the column and row descriptions or headers. Click Insert> Click your preferred chart category and then click your preferred chart type. There are many great options here. I often click on Recommended Charts to see what options Excel will offer. I really like this line chart as it shows the sales trends for each rep. Notice Pam’s sales are rising steadily throughout the first half of the year? Interesting!
Simplify a Chart: In many cases, the chart will be easier to understand if you delete the features that make it look busier including lines and labels that do not add value. If you click on the chart, you will see the modification options on the upper right side of the chart. Starting with Excel 2013, users can more easily use three design tools to modify charts.
The Green Plus Sign allows you to remove elements from your charts like Data Labels or grid lines. The paint brush lets you set a chart style, and color scheme and the Filter Icon lets you edit what data appears in your chart. That’s it! These are the best excel tips ever!
Our training is available in all formats including live, keynote, webinar, and e-learning. Email me today for more info or complete the contact form on this page for more info! We offer annual subscriptions to all our content. Just contact us for more info on an enterprise or team license at firstname.lastname@example.org Effective business communication will help every member of your team become more productive!
Get Control! will help us all get better at Excel! They have the best Excel tips ever!