what if I told you there are Excel tools you're probably not using that could instantly improve your workflow hidden with in EXL sometimes in plain sight are gamechanging tools that most users overlook today I'm going to show you how to master them to transform your productivity now my favorite is number three don't forget to let me know in the comments which one you like the most and how you plan to use it I've also included the example file and you'll find the link in the video description if you work with large spreadsheets that are particular wide you might have a habit of hiding columns so that you have more space to work with but then if you need them you have to unhide them and then hide them again and it can become tiresome a quicker way is to Simply select the columns or rows that you want to hide and then on the data tab of the ribbon click group and now I've got this group button I can quickly hide and unhide The Columns as I need you can have multiple groups you can even have multiple layers of groups so for example I can group these columns as well and now I can just hide them or I can hide all of them we also have these buttons on the left here that quickly collapse all and expand all buttons and you can have up to eight groups now if you need to ungroup any columns or rows simply select them click ungroup and again until they're all gone now for homework you can try grouping the rows most people use past special to paste formulas as value but there are a load of other super useful tools hiding in plain sight in the pce special dialog box that you could be missing out on here I have a series of negative values that I want to convert into positive values and I can do it easy enough by adding a formula that multiplies the value by minus one and then I copy it down and then copy that across and paste it as values but that is a load of work so the quicker way to do it is to type in an empty cell minus one copy that cell select the values paste special multiply job done what if you want to convert these values to percentages well we can apply the percentage formatting but that's usually not what you'd expect so let's undo that an easy way is to type 1% into a cell copy that select the cells paste special multiply click okay and now they're percentages here I have a series of values that I want to copy across to column H now I could copy them one by one skipping the cells that don't have values but the quicker way is to select them all contrl C to copy select the range you want to paste them to paste special and then skip blanks click okay now I can clearly see column H is updated without affecting the cell sales containing blanks that were also copied so there you have three useful pay special tools but take some time to try out the others for homework the copying and pasting tips don't end there normally we copy and paste then if we perform another action the clipboard is cleared but there's a little known way we can store multiple items on the clipboard for pasting whenever we need on the Home tab of the ribbon we have the clipboard group of tools with copy paste Etc and in the bottom right of this group we've got an icon that opens the clip board this opens the clipboard and a tus pane on the left and when the clipboard is open it keeps a list of up to the last 24 items that you've copied or cut and these can be from any app across windows and include text values images Etc it's super handy if you want to repeatedly paste multiple items so for example here I might want to put some emojis beside values that I'm happy with and others that I'm unhappy with with and I can do that quickly and easily without having to constantly copy and paste or insert emojis from somewhere else now you can keep the clipboard tidy by clicking on the drop down here and deleting any items that you're not going to need to use again alternatively you can click clear all and start from scratch and in the bottom left we have options and here there are various settings for how you want to work with the clipboard including an option to have it open automatically when you press contrl C twice you'll find this same clipboard in all office apps but there's also a Windows clipboard that's independent you open it by pressing the Windows key and V this opens a separate task pane that floats above the canvas and to insert an item simply click on it once and in it goes however once you select an item or you click away from the windows clipboard it closes you've got a Windows V again to open it which can be a bit of a pain however one of the nice features we have with this clipboard is we can pin an item now this doesn't pin it to the top of the list but it does retain it in the event that you clear the clipboard by pressing clear all while we're here notice we also have symbols and they're grouped into most frequently used General punctuation currency symbols and more we also have kimojis gifts emojis and most recently used shout out to fellow MVP win Hopkins for the windows V shortcut that opens this task pane now if you like the floating feature of the task pane you can always left click and drag the clipboard out and now it's a floating task Pane and the thing I like about this clipboard is it doesn't disappear if you select a cell in the worksheet we often need to hide sheets in our workbooks to keep them tidy by hiding workings and prevent users from accidentally breaking our formulas or to keep information confidential but there's a problem with the way most users hide sheet let me show you when you right click and hide a sheet it's no longer available to navigate to which is great however if you rightclick any of the other sheet tabs you'll notice unhide is now available and this is like a red rag to a bow as some users will be intrigued to know what you're keeping from them and they can simply click on it and unhide the sheet now you can protect the workbook structure so for example if I hide the sheet and then on the review tab I can protect Tex workbook I can even add a password and click okay and then if I rightclick any sheet tabs both hide and unhide are unavailable but there's another way I prefer to do it which doesn't alert the user to the fact that there's anything hidden so let's unhide that and we'll try again for this you'll need the developer tab if you don't see it right click go into customize the ribbon and then make sure developer is selected here and click okay what we want to do is go into the Visual Basic editor alternatively you can press the keyboard shortcut alt f11 this opens the Visual Basic for applications task pane in here you want to locate the sheet here that you want to hide and then in the properties pane below and if you don't see it go to the view Tab and choose properties window and then at the bottom we're going to change the visibility property from visible to very hidden and if you keep an eye on the sheet down below you'll see it disappears and now if we go back to Excel and right click you can see unhide is still grayed out so the users are none the wiser that anything is hidden now to bring the sheet back go back into the Visual Basic editor select the sheet from the list click on the drop down and change it back to sheet visible and now it's back of course none of this is entirely secure because Excel sheet and workbook protection can be removed so don't rely on this for super confidential data when working with data it can be timec consuming to interpret key information in a sea of numbers and we risk missing key insights thankfully Excel has some clever ways we can make our lives easier here I've calculated the year on-year percentage change and while it clearly shows which are positive variances and negative this may not be intuitive for someone who doesn't work in finance another way we can visually indicate the direction of the change is with symbols we'll start by selecting an m cell and then on the insert tab of the ribbon click symbol and here make sure geometric shapes is selected in the subset and then I'm just going to double click on the up triangle and the down triangle to insert them you can see them in the cell behind click close now I'm just going to select them and contrl + C to copy them to my clipboard I'm going to escape out of there what I'm going to do is apply a custom number format to these cells so control one to open the formatting pane and then custom and up here it's already got percentage applied so I'm just going to modify this format I'm going to add a space contrl + V to paste in my symbols now this is my positive percentage and then I'm going to add a semicolon and that's going to separate it from my negative format which is going to be a percentage with a downward triangle and you can see we get a preview for the first cell and then click okay and just like that I now have visual indicators that clearly show whether the change is upward or downward custom number formats are a vast topic with so many cool things you can do and I've put together a comprehensive guide and cheat sheet that you can download from the link in the video description so be sure to grab that another tool we can use to make it quick to interpret our data and identify patterns is conditional formatting and there are loads of built-in options for quick results taking the previous example we can make it even quicker to interpret by color coding the variances I'll start by selecting the cells then on the Home tab of the ribbon conditional formatting highlight cells rules greater than the cells greater than zero I'm going to format with a customized format that is with a font color of this dark green click okay and okay and then let's repeat for the negative variances so cells that are less than we're going to put zero click on the drop down customize format and in here I'm going to go with purple you could go with the traditional red and green for good and bad but this purple color is more easily accessible for those with color vision deficiencies click okay and okay now of course you could set up a third format for zero values if you want if I click away you can see with the color coding it's even faster to inter and easily pick out the negative results that might need our attention another way we could visually indicate the variances is with conditional formatting icons I'll select the cells and then again on the Home tab of the ribbon conditional formatting icon set and we've got this up and down triangle here I'll select that one and then I'm just going to go back in and manage the rules click on this one edit rule it's currently applying the formatting based on a percentile so we've got 30 % that's a positive change set to neutral so we need to change that we do that by choosing number here so when the value is greater than or equal to zero it's going to get a green triangle and then we want to change this also to number when it's less than zero and greater than or equal to zero it's going to get the yellow Dash and when it's less than zero it's going to get the triangle I'll click okay and okay and there we go now unfortunately you can't change the color of these triangles so if you need need to make your files accessible then the previous example with the symbols is better another thing I like about using the symbols in the custom number formats is you can also use these in chart labels as you can see here whereas there's no conditional formatting for charts themselves conditional formatting is another tool that has Myriad of built-in options but the real power comes when you know how to use formulas to apply formats so in this video I take you under the hood to reveal how Excel uses formulas in conditional formatting so check that out next I'll see you there