Pro Excel Chart Tips for Rapid Report Creation!

in this video i'm going to share some of my favorite chart formatting tips to help you build reports and dashboards super fast at the end of the video let me know in the comments which was your favorite tip or if you have your own favorite tip i'd love you to share it when you're building a report or dashboard it's important that the charts have a consistent look and feel and there's a couple of ways we can achieve this the first is to build one chart and then copy it and edit the range being referenced for example let's say we want a separate chart for each region i can start by inserting a chart for europe or just to insert a line chart let me bring it over here and i'll make it a little smaller now notice it's detected the year as one of the series and it should be on the axis so i'm going to right click and select the data we'll remove it and then we'll edit the axis labels to reference the years click ok and okay let's say i also want this line to be a different color so let's go with blue and i don't need the legend because the chart title already says what the region is so we'll delete that and that chart's done now i can either insert another chart and make all those changes again from scratch or i can copy the chart that's already nicely formatted so with the outer edge selected ctrl d will duplicate the chart let's bring it over here now all i need to do is change the cells that it's referencing to pick up the americas but notice that the line has changed back to the default green color and this is due to a setting in the options that's on by default so i'm going to control there to undo that change and we'll go into the options and then on the advanced tab i'm going to scroll down to charts and here properties follow chart data point for the current workbook i'm going to uncheck that box click ok now when i change it to america's it keeps the formatting so that's just going to save me a little bit of time and big thanks to fellow microsoft mvp john peltier for teaching me that tip now i'm just going to hold shift and select both of the charts so i can copy both together ctrl d to duplicate let's drag them down here and we'll just change one by one so first of all this should be asia and lastly this is africa so there's my four charts they're all consistently formatted with blue lines and no legend now what happens if your boss says they want all the lines to be a different color so let's select a different color i'm going to make this on back to green they want the chart title to be left aligned they want labels on the data instead of the vertical axis so i'm just going to select the vertical axis and delete that and they don't want the grid lines now that's a lot of changes to repeat for each chart and a quick way we can copy the formatting is to select the outer edge of the chart ctrl c to copy it select the next chart and on the home tab we're going to paste special and here we just want formats so you can see it's copied all the formatting with one go so i can just select the next chart and press the f4 key to repeat and likewise for africa now you'll notice that the three charts that i copied the formatting to now have this dark gray border around them and that's an artifact of the copying and pasting it's a bit of a bug in excel so let's go and fix that so we want the outline to be this pale gray color and i can't use f4 here to repeat that formatting but i can just click the shape outline button now that i've selected the color once it's kept it there so i can just click it once to use it again now just a word on pivot charts copying the formatting on pivot charts is slightly different we copy it and then select the next chart you'll notice there's no paste special option here it's there but when you click it nothing happens with pivot charts you simply paste so ctrl v to paste and that's done and unlike the regular charts you don't get the dark gray border around the chart when you copy the formatting now another way you can quickly change the colors is using a color theme on the page layout tab and then colors and as you hover your mouse over the color palettes you get a preview of what it might look like now you can choose from these default themes or you can create your own custom theme maybe based on your company branding just keep in mind that these color changes are applied to the color of shapes and cell fill so this is not just for the charts charts are objects just like shapes images and form controls you can select multiple objects by holding down control or shift to select them and then if you want to deselect one just left click again to deselect while holding down shift or control you can also select all objects by selecting one and then ctrl a to select them all you'll notice though that this also selects objects like shapes and images as you can see here i can deselect that holding down shift and left clicking it now sometimes objects might be overlapping one another or tricky to select you can display the selection pane via the page layout tab and then selection pane let me drag it over here so it's closer to our charts we can click on the eye icon to hide a chart we can hide them all or show them all notice the picture is also in this list because it's an object you can also click on the names of the objects to select them and if you hold down control you can select multiple once they're selected you can move them around and apply alignment formatting and the like you can even resize them if you're working with lots of objects you can give them a better name so double-click type in the new name and press enter another way to quickly select multiple charts or objects particularly objects that are layered on top of one another is by turning on select objects so by the home tab of the ribbon find and select you want select objects this turns your mouse cursor into a lasso you can left click and drag to select multiple objects only those objects that are entirely circled in your lasso will be selected once the chart or object is selected you can go in and choose design options and formatting your mouse remains in the select object state you can't select any other cells a shortcut to turn off select objects is to press escape and your mouse returns to its original state now that you know how to select your charts quickly and easily you'll want to make sure they're nicely aligned so i'm going to hold down shift to select multiple charts and then up on the format tab under alignment i can center align these and i might want to do the same for these two align to the center we can also align them horizontally you can see there's lots of options if you have three or more charts horizontally or vertically aligned so let me bring this on over here i like to use the tool in the alignment that will distribute them in this case horizontally and what that's going to do is just put an even space between each of the charts if you hold down the alt key it's going to snap the chart to the grid while you move it around and it will also work when you're resizing it so you can quickly align charts to the grid and make sure they're all uniformly sized using the alt key once you get them exactly as you want you can go to the view tab and turn off grid lines another way you can copy charts is by holding the ctrl key while you left click and drag so with the chart selected and i hold down control and put my mouse right on the edge of the chart you can see i now have the plus sign if i left click and drag you can see i'm getting a copy of that and if i hold down shift at the same time it will remain aligned to that horizontal axis of course you can also use the alt key with control while you snap the new chart to the grid if you want precise movements you can use the arrow keys to just nudge the chart up and down or left and right but just note if you have excel 2013 then you need to hold down control while you use the arrow keys that's not relevant in excel 2016 onwards we have the luxury of just being able to use the arrow keys without control the most common way to select chart elements is with the mouse but sometimes an element might be tricky to select like the series here for small values it's very difficult to get my mouse in exactly the right place an easy way to select elements like this is via the format tab and then in the drop down here i can choose it from the list you can see it's now selected because i have the pull handles on those columns and from here i can control 1 to open the formatting let's drag it into view i can go about formatting that series perhaps changing the color another way you can cycle through elements is by holding down the ctrl key while you use the arrow keys and you can see it's cycling through the different elements in the chart one of the most annoying things when building a dashboard is needing to change column widths or row heights and as you do that your chart gets messed up as well we can prevent this in the chart properties so selecting the chart control 1 to open the formatting if it's not already open and then in the properties under the properties drop down instead of move and size with cells you can choose move but don't size with cells or don't move or size with cells with either of those selected your chart isn't going to change the size as you expand those row heights or column widths now if you regularly use the same chart types and find yourself repeating the same formatting over and over then you can save time by saving the chart as a template to do this select the chart right click save as template it's going to open up to the location of your chart template give it a name and click save to use the chart simply select the data insert tab you can either choose recommended charts or click the chart dialog box launcher and then under all charts templates you'll find the templates that you can choose from i hope you found these tips useful if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 22,360

Rating: 4.9886899 out of 5

Keywords: excel chart, excel chart fomatting

Id: 46CGcyP4dbo

Length: 11min 53sec (713 seconds)

Published: Tue May 11 2021