30 Essential Excel Tips and Tricks for 2024 - Excel Productivity Tutorial

[Music] Simon Says subscribe and click on the Bell icon to receive [Music] notifications hi there everyone and welcome to a very important lesson in this video we're going to learn how to highlight the active rows in which you're looking the data for and this is going to help you avoid the hassle of going back and forth in the rows to check whose data you were looking at so for example if you're looking at 64,000 so you can see that the whole row got highlighted and it was very easy for me to find out that it was Mr Williams data so let's jump in without further delays into how we have to create the highlighting in the active rows so here I have a similar sheet in which the active rows is disabled so the first step is going to be click on contrl a so that the whole worksheet is selected once once this is done you got to go to the homes Tab and in the homes tab there is a section of styles containing an option of conditional formatting click on it go to new rules and once this popup appears select the last option which says to use a formula to determine which cells to format once you click on it it Demands a formula to be applied so you got to follow and add the same formula which I am adding which is equals to Row Bracket open and close equals to cell bracket open with opening quotation marks row quotation marks close and then bracket close once this is done you go to format and you select any color you want if you want something catchy like yellow or red that's entirely optional but I would rather go with pastal colors which is the peach one click on okay and once you click okay you can see that your one whole row is highlighted but what about the rest of them don't worry what you got to do is go to the sheet rightclick and go to view code once you go to view code and you click this sort of worksheet might appear in front of you if this worksheet doesn't appear then you will have something like this which is a journal so you can simply go to this dropdown click on worksheet and just between the end sub and the private sub you got to add Target do calculate so once you've done this close the tab close this dialog box and now whenever you select on any row you're going to see that the row gets highlighted that was all for this lesson I hope it proves helpful for you thanks for watching in this lesson we're going to discuss merging cells and merging cells is quite a controversial Topic in Excel so I'm going to show you how to merge cells I'm going to show you some of the issues that this can cause and then we're going to take a look at a better way of doing it so let's start out by talking about exactly what we're doing when we merge cells now you can see that I have a data set just here this is just a selection of the columns from the dat set that we were using previously and you'll notice here that in cell A3 we have sales 2011 and then we have some records that relate to 2011 sales we then have sales 2012 and the same thing sales 2013 and then sales 2014 so these are going to act like headers just to break up the data and make it really obvious which sales pertain to which years now we could leave these little labels where it says sales 2011 for example or in the cell that they're currently contained in so this is in cell A3 currently however this would look a lot better if it was kind of centered across our data so somewhere in the middle here now when you type something into a cell it is contained entirely within that cell so if you're thinking that you can maybe select all of these cells and then in the alignment group click on Center it's only going to Center that text within the cell that it's contained in it's not going to center it across the selection so a way that people generally tend to get around this and get the effect that they want is to use the merge and center option because what merge and center will do is it will merge together all of the selected cells and effectively make them one cell so you can then use your horizontal alignment tool to Center the text into the middle of this big merged cell so if we go to the Home tab in the alignment group we have a merged merge and center option just here now it's worth noting that if we click the drop down we have a few other options in here but the one that we want is merge and center now as soon as I do that check out what happens the individual cells now all become one cell and this cell is effectively A3 and because we chose to merge and center it centered that text in the middle of the merged cell so what we could then do is maybe change the background color of this to let's go from a light purple and that kind of gives the effect that I was looking for I could then do this for the rest of these headings now of course once you've done it once you can use the format painter to copy that formatting so I'm going to click incel A3 let's double click on the format painter let's paint that across just here and we're going to do the same for this one and then if we scroll down we're going to do the same for this one as well and escape to deactivate the format painter so that is the exact effect that I was looking for and we've achieved it very simply using merge and center however there is a caveat to using this method when you do use merge and center it then becomes a lot more difficult to make cell selections and it can sometimes cause problems with any formulas that you have so let me show you what I mean if I now decide that I want to go in and I want to select all of the value use in the quantity column check out what happens if I click in cell F2 and start to drag down to make my selection it won't just let me select the quantity column because we have that merge cell in there effectively one cell it expands to the width of that and then it kind of uses that width the entire way down even if I was to use a keyboard shortcut contrl shift down arrow you can see it only goes to there and I would need to do it again and again and it just doesn't work well with this merged cell sitting in there so making selections then becomes a problem now we can get around this and achieve the same effect but using a method that still allows us to make selections and ensures that our formulas will still work correctly so let's undo and try this again so we're back where we started and what we're going to do is we're going to select this row of cells but instead of going to merge and center we're going to open up our alignment settings we're going to go to horizontal alignment and if we click the drop down we have an option in here called Center across selection so if we click on this and choose okay it's going to do something very similar the cell looks like it's one cell and it centers that text in the middle so I can then go in and I can apply my background fill color and I get exactly the same effect the difference here is that it hasn't actually merged the cells it just looks like it it has notice that I can still click in cell A3 and it is just cell A3 I can click in cell B3 C3 D3 E3 so on and so forth so these are all still individual cells they haven't been merged into one big cell called A3 and what that means is that if I want to go in and select the quantity column I can still do that because we still have our individual cells there so this method gives us the same nice effect but it causes us far less problems now I have seen some people particularly when this topic is discussed on LinkedIn say that there are certain scenarios where merging and centering is better than centering across selection and you may well come across those in your working life but in terms of the look and feel and also the usability my recommendation is to go with Center across selection whenever you can so now we can simply double click the format painter and copy this formatting across to to the other rows and just to finish off this lesson when we were looking at merge and center if you click the drop down notice that we have other options in here as well so if you are going to use this method you don't necessarily have to merge and center the text you could just choose to merge across so this will merge all of the selected cells together but it's not going to Center the text the text will stay on the left hand side where it was originally we can choose to just merge cells so that's just going to merge the cells together it's not going to do anything with the text and then we have an unmerge cells option which will basically allow us to come out of that merge mode and split the big merg cell into separate cells again so just be aware that you do have other options in there aside from merge and center have a little play around with those have a little practice and I will see you in the next lesson hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to track changes in Excel sharing your workbooks with your team may introduce unintended errors or data mishaps in your spreadsheets to save your precious time and effort from such accidents Excel has a new track changes feature so let us now see how to use the excel track changes feature easily to enable the track changes to feature go to the review Tab and in the changes group click on track changes and then click on highlight changes in the Highlight changes window click on the track changes while editing this also shares your workbook option and click okay here you can also specify which user can edit the spreadsheet in the when Who and the where sections that's all you have to enable tracking changes in Excel Hereafter Excel will track changes made in the workbook however one prerequisite to enable the track changes in Excel is is to always save the Excel book beforehand sometimes it is advisable to list all changes in a separate sheet for clarity especially if there are too many changes to track to do this save your workbook go to the review option click on track changes and select highlight changes in the Highlight changes window click on the list changes on a new sheet option and click okay now all your changes will be displayed in a separate sheet the changes made in a workbook are not final as long as the tracking mode is enabled you have to review them and either accept or reject these changes to do this go to review under the track changes option and click on accept reject changes in the select changes to be accepted or rejected window you can specify which changes you want to review enter the inputs into the when who and wear boxes and click okay when accept specific dates or statuses like not yet reviewed who accepts specific usernames or everyone where accepts a range of cells that you need to review in the next accept reject changes window you can accept or reject changes one by one or in one go if you reject a change the original value will be restored if you accept a change the new value will be finalized and it cannot be undone finally to disable the changes go to the review Tab and click on track changes and highlight changes uncheck the track changes while editing his also shares your workbook option and click okay once you do this Excel will no longer display the blue colored highlights and will stop tracking changes made in the workbook that is all everyone implement this technique before you share it with your team members and avoid errors thank you in this lesson we're going to take a look at another really useful option and that is freezing pains so what exactly is freeze pains well if you take a look at this spreadsheet you can see that it's reasonably long we have quite a few rows of data and notice as I scroll those column headings disappear off the page so where we have employee name Department year salary bonus and new salary if I was to scroll down I can no longer see those headings so if I was down here for example it's pretty hard for me to know what these values in columns d e and f are representing without those column headings being there so this is where freeze pains can come in really handy what we can do is we can scroll up to the top and I'm just going to click in cell A1 where we have our heading row then if we go up to view notice in the window group we have a freeze panes option now we can click this drop down and you can see that we have three different menu items in here now notice we have freeze panes freeze top row and freeze First Column so if I simply wanted to freeze that top row I could select this option and what that means is that when I now scroll that row is going to stay put so I can always see those column headings so that can be super useful if you want to unfreeze you can click the drop down again and just choose unfreeze panes and we're back to normal so freeze top row is pretty self-explanatory but what do these other ones mean well freeze First Column that's pretty easy to understand it will do exactly the same as freezing the top row but it's going to work with the column so if I wanted to freeze the First Column so that's always visible when I do a horizontal scroll I could choose that option from the menu and now when I scroll across you can just about see that that employee name column is staying put and once again if you want to unfreeze we just choose unfreeze panes now the final option which is simply freeze pains this essentially allows us to customize where we want to freeze the panes so if we want to freeze maybe multiple rows and maybe two columns we could use freeze panes to Define that now the important thing to note here is that you need to click your mouse wherever you want to freeze the columns and the rows for example if I wanted to freeze let's say the first seven rows so everything above where I'm clicked and also freeze the First Column I would need to click in cell B8 because then when I click on freeze panes it's going going to freeze the top seven rows but it's also going to freeze the employee name column so where your clicked is super important similarly if I was to click down here in cell d18 and choose freeze panes it's going to freeze everything above and everything to the left so now you can see that we have the first three columns Frozen and if I scroll down I have the first 17 rows also Frozen and visible so you can really customize this and set it up to suit your needs the most common one by far is to Simply freeze the top row I'll leave you to have a little experiment and a play around and I'll see you in the next section hello everyone welcome to a new tutorial from Simon says it in this video tutorial I will show you how to use split screen in Excel Excel only allows you to view more or less 28 rows and 23 columns but what if you have hundreds of rows and columns and want to compare them in such a case Excel allows you to split screens to enable you to visually compare any data when you use the split screens in Excel each pane grants you the ability to independently scroll and compare the data this helps you to view each part of the same worksheet independently let us now see how to use the split screen in Excel first let us see how to split the screen horizontally and compare the data select the first cell or the entire row where you want the split to occur always remember that the split always happens above the selected cell or row navigate to view in the main menu under window click on split this splits the Excel sheet into two parts the upper and the lower you can see that there are two scroll bars for individual panes you can use them to scroll the sheet independently and compare the data you can also change the size of the pane by moving the split bar up or down place the mouse pointer on the gray bar to turn the normal pointer into a double-sided pointer click drag and drop where you want to position the split bar in the same way you can split the Excel sheet vertically to split the screen vertically select the column or the first cell of the column you want to split navigate to view under window select split this splits the sheet vertically the partition can be seen by a Gray Line always remember that the split always happens to the left of the selected column in most cases you might only have to split the data horizontally or vertically to compare the data however in some cases you might have to compare the data simultaneously in those cases there is a need for a four-way split to split the screen both horizontally and vertically just select the cell you want the split to occur in this case When selecting the cell the split occurs to the left and above the cell in the same way navigate to view in the main menu under window select split the four-way split allows you to scroll the sheet in a horizontal and vertical Direction independently until now we have seen how to split the cells once you have viewed and compared the values you might want to unsplit the screens to the original format to unsplit the screen navigate to view under Windows click on split make sure that you are working with an already split screen this removes the split and reverts the Excel screen to its original format that is all everyone in this video we saw how to split screen in Excel using Excel split screen provides an easy way to view compare and contrast values that will be hard to find when there are large amounts of data thank you hello everyone welcome to a new tutorial from Simon says it when you take a poll create a survey or make a quiz to understand others perceptions these fillable forms help you in a variety of ways one important element of a fillable form is the radio button also called the options button in this video tutorial I will show you how to insert radial buttons in ex so without further Ado let us get right into it one of the foremost requirements when you insert radio buttons in Excel is to enable the developer tab to enable the developer tab open the Excel worksheet and right click on the ribbon make sure to click between the ribbon option rather than the space after the options select customize the ribbon this opens the Excel options dialogue box in the dialogue box click the check box for develop Vel ER and click okay once the developer tab is enabled let us see how to insert a radio button in Excel to insert a radio button navigate to the developer tab under the controls section click on the drop down from the insert and click on the option button this changes the default Mouse pointer to a Crosshair like pointer left click and populate the radio button with a default width you can see that the populated button has two names one in the alt text which is visible on the worksheet which you can click and rename it the other name can be seen in the name box called The backend name which does not change even after you rename the radio button hold the control key and click on the radio button this outlines the button you can then use the anchor points to move or resize the radio buttons you can double click on the text to rename the text when you move or resize the button you can hold the alt to snap the button to the dimensions of the the grid lines now click away this exits the selection mode Hereafter you can now click on the radio button to check it this inserts one radio button in the same way you can add multiple radio buttons inserting the radio button only adds the button on the screen there is no validation or data to serve as a value for the selected radio button in this case linking a radio button provides definitive data in the reference cell based on your selection to link the radio buttons to the cell right click on the radio button and select format control navigate to the control tab you can see if the radio button is checked or unchecked from the value option to link a cell enter any cell number in the cell link text box or you can click on the small upward and select a cell click okay let us now see how to group options in Excel now navigate to the developer ribbon from the insert dropdown click on the group box option this changes the default pointer to a Crosshair now outline the options that you want to group together change the name of the group by double clicking on the cells or right clicking on selecting edit text select all the options including the grouping box then right click on it and select group when you group options together the selection will only pertain to that particular op option and not the other options in the other cell using the above mentioned methods create the radio buttons create a group box link the cell to a reference cell and finally right click to group the cells this way you can independently select the options from different groups and they'll reflect in the referenced cell in case you're not satisfied with the option listed for the radio button or you want to delete the whole options radio button there are a couple of easy methods to do that to delete a radio button you have to first select the option by holding the control key this highlights the radio button instead of just selecting the radio button if you want to highlight all the options in the radio button select one option and press ctrl+ a now press delete to delete the selected options in this video we saw how to insert radio buttons in Excel we also saw how to link the radio buttons group them and delete the radio buttons radio buttons are a useful feature to know the user validation and Ed to perform certain functions and operations thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to wrap text in Excel Microsoft Excel is designed primarily to store numbers sometimes for longer numbers or text you'd want to fit the content to it cell in those cases you can wrap the text to the cell so without further ado let us see how to wrap textt in Excel the first way to wrap a text is by using the wrap text option from the Home tab select the cell that has the text to wrap and then click on the Home tab in the alignment group select the wrap text option in the ribbon this automatically wraps the text to the next row in the same cell instead of adjusting the row height you can adjust the width of the column and wrap the extended text in Excel when you place the cursor on the border of the column containing the cell you can see the cursor changed to a double-sided Arrow drag it to the end of the text and see the text wrapped to the cell another simple method to wrap text in Excel is by using the Excel hotkeys press the ALT key to enable the shortcut layout and again press the keys H and W one after another on your keyboard to wrap the text you can also use the format cells option to wrap text in Excel first select the cell and right click on it choose the format cells option choose the alignment tab from the format cells dialog box in the text control section check on the wrap text check box and click okay Additionally you can auto adjust the width of any cell easily to wrap the text in Excel if you don't want to drag The Columns to auto adjust the cell width or height select the cell with the text select the Home tab go to the cells group click on the drop-down from the format option choose the option autofit column width to adjust the cell width and extend it to fit the text and finally you can also choose to add manual line braks to the text to wrap it select the text in the formula bar place the cursor in the place where you want the line to break into the new line and press alt plus enter however if you do not want the line Brakes in your Excel spreadsheet sheet you can choose to remove them using the find and replace option on the spreadsheet press ctrl+ h to open the find and replace dialogue box press ctrl+ J in the find what text box to add a line break character then leave the replace with text box empty and then press the replace all button this removes the line bre character that you have included in the text that is all everyone the function wrap text in Excel displays a long text on multiple lines in a single cell wrap text inserts a line break either automatically or manually in this video we saw how to wrap text in Excel in six easy ways and we also saw how to remove them thank you let's continue where we left the previous module in the previous module I mentioned that we'll be looking at how to create a pivot chart just like a pivot table pivot charts are very easy to work with so let's start off by clicking on the pivot table when you click on a pivot table the pivot table option appears and over here in the tool section you see that there is the pivot chart option if I click on the pivot chart we have a number of charts that can be created with a click of a button but you really need to make sure what makes sense for this particular data set column charts are very easy to understand and create in the column charts you have the clustered column option if you click on the chart you can see how exactly would the chart look like so you will have the sum of Revenue in blue the sum of quantity in Orange and the sum of total contract value in Gray now it's important to understand that we have the sum of quantity the sum of Revenue and the total contract value in this pivot table so the sum of quantity is in thousands and the sum of revenue and the total contract value is in hundreds of thousands so a clustered column chart would not work in this case because the other two fields are in hundreds of thousands so how do we visually represent this kind of data let me select a clustered column chart first hit okay so the chart is visible now we don't see quantity in our chart so in order to show the quantity as well here's what I need to do so I'll hit the plus sign click on the format axis and I'll click on more options over here now in the axis options as I want to update the sum of quantity series I'll hit sum of quantity and I can select the secondary axis that will pop up over here so what this will do is I will also be able to see the sum of quantity on the secondary axis as you will notice the sum of quantity is currently being plotted on the primary axis which is in hundreds of thousands of dollars so quantity being in thousands is not visible at all so let's select secondary axis and now the quantity is also visible per product let me make this bigger we now see the quantity but it does look a bit odd the quantity column bars are extremely broad as compared to the sum of Revenue and a sum of total contract value so we can reduce the Gap width by typing keeping our preferred Gap width over here it's currently 199% and that's why it's wider than the other series that we have let's reduce the Gap width to 5% let's see how it looks much better Gap width has now reduced and we can now clearly see that when we talk about the thermal imaging camera we have over 500 units of thermal imaging cameras 516 to be exact and then we have we have the super tracker the software license and so forth we hardly have any GPS trackers so the company might want to order some more GPS trackers to have some inventory on hand so using the clustered column chart you can visually see what the status is of Revenue total contract value and quantity by product let's have a look at some other charts so if I click back on the pivot table and I go to the pivot chart option we can also create a line chart let's see how that looks like I'll just move this to the right we have a line chart over here and for this line chart let's say for example we are interested in products across all contract lens so there is a filter button over here I click on this filter button and I select the other contract lengths which were not selected previously I I hit okay and now everything is unfiltered so just by unfilter the data in one chart all the data is unfiltered in the pivot table you can see that the contract duration now shows all please note that we have the same issue over here the sum of quantity now needs to be plotted on a secondary axis everything in the line graph is being plotted on the primary axis so you can easily fix that same way we fix the first chart another commonly used pivot chart is the pie chart so if I go back to the pivot table analyze option and I hit pivot chart I can select the pi option and I can hit okay and now you have the sum of Revenue by product if you hover over the various colors you can see the values so the blue represents 9% of the total value or or $78,400 what if we want to see these amounts in the chart we don't want to hover over them to identify what the amounts are so we can easily add those by clicking on the plus sign here and adding data labels if you want to do it in percentage terms you click over here you select more options you go on label options and you select the percentage option which has been unchecked you check it so the percentages are now being shown in the chart if you just want to see percentages just uncheck value and you get percentages in your chart if some of the values are not visible you can either move it outside the chart or you can change the colors and make them lighter so you can clearly see the numbers pivot charts are extremely easy to work with they make data analysis very convenient in this module we discussed three of the most commonly used pivot charts namely clustered column line graph and a pie chart with a little practice you can create meaningful charts and process your data in the previous lesson we saw how we can import a folder of files and also a text file into Excel and it's this text file that we're going to clean up and prepare for analysis now if you find that when you are opening these files you get this security warning running across the top in yellow then you can simply click enable content it's just because we've created external links when we've imported this data so I'm going to click enable content and I'm going to say do not ask me again for network files so now we have this data set and the first thing we're going to deal with here are these blank rows I'm also going to show you how to handle blank cells and how to remove duplicate entries so let's start with the blank rows first of all now blank rows in general can cause problems when you're analyzing your data for example if I want to put this data into a pivot table and then start creating things like charts if I have blank rows in there it's going to throw off my data so you always want to make sure that you remove any blank rows that you have in your data set now if you have a very large data set and you have quite a lot of blank rows in there it's going to be pretty tedious to go through and try and delete all of these manually so I might decide I want to select these two rows hold down control select these two scroll down select some more that's not particularly time efficient fortunately there is a quick way to do this in EXC so all we need to do here is Click somewhere in our data go up to the Home tab and then all the way over in the editing group if we go to find and select and then go to special this is going to open this little goto special dialogue box now if you're interested in keyboard shortcuts there isn't a keyboard shortcut to go directly to go to special but what you could do is press contrl G and then click the special button now what this allows you to do amongst other things is Select different items within your spreadsheet and one of the options that we have here is to select all blanks so let's select that click on okay and it highlights all of those blank rows so now with them all highlighted I can go up to that Home tab again into the cells group and using the delete drop-down I can delete sheet rows and I've deleted all of them in one go so that's going to save me a lot of time now another thing you want to make sure you deal with in your spreadsheets are blank cells now I don't have any blank cells but if I did let's just delete a few things out of some of these cells let's go like that like so now again blank cells can cause a bit of a problem it's always better to have some kind of numeric value in these cells even if that is just a zero so once again if I want to select all blank cells in this particular column all I would need to do is select the column contrl G special and I can select blanks and click on okay and that will select all of the blank cells just in the range that I specified so now I can enter a zero into all of them in one go so all I need to do here is type zero control enter and that's going to put a zero in all of those blank cells I haven't had to scroll through and do them all individually so again that is a real time saer of a trick and the final thing I want to show you here is removing duplicates from your data set so it might be when you import your data in some duplicates come across for whatever reason now when I say duplicates I mean an exact duplicate of every single column in this row now it's always worth removing duplicates even if you're not sure if you have duplicates in your spreadsheet and in Excel we have a button that can check duplicates for us and remove them quickly so let's make sure we clicked somewhere in our spreadsheet up to the data Tab and in the data tools group we have a remove duplicates button now when I click this it's going to ask me to determine what is actually a duplicate and the first thing I need to make sure that I select is my data has headers and notice it's picked up all of the different columns and it's put a tick in the box next to all of them so what this basically means is that every column has to be the same for it to be considered a duplicate now I'm happy with that because I only want to remove duplicates where every value in the row is duplicated so let's click on okay you can see that it's actually found two duplicate rows or values and it's removed them click on okay and we are done so that is how you can very quickly delete blank rows input data into blank cells and also remove duplicates hi there everyone and welcome back to another lesson in this video we're going to learn how to create a drop-down menu in Excel drop- down menus are very convenient to use and they help you update the statuses of your tasks almost instantly so let's get started and learn how to create one one so the first step is that you create a key for yourself which you're going to use to create the drop- down menu now the options that you offer in the drop- down menu are entirely dependent upon the type of tasks that you make in my case I will be going to sheet two where I have created my key there will be three options that I will be offering in the drop- down menu which are complete incomplete and in progress the second step is that you select all those cells in which you want the drop- down menu to occur in my case it's going to be cell D6 till D12 once you have selected it you go to the tab of data then go to the section of data tools and go to data validation when you click on data validation you have a popup that appears like this in here you got to select a list and you got to give a source for it that where is the whole list being taken from so in this way you're going to go to sheet two because our key is on sheet two you're going to select all the cells which are needed and click on okay that is how simple drop- down Menus can be made and now you're going to see that every cell that we had selected has a drop down menu sign coming with it so once I click on it I'll have all the three options I can select any one of them and update the status the next thing is that it can be pretty hard for you to go look at a long list of complete incompleted in progress if they are not colored so now I'm going to show you how you can color these box for each one of these options let's go with complete first let's say Mr Hassan from marketing has completed his task and I want that every time I click complete the Box turns green so for this for the cell to turn green I have to add conditional formatting for that you got to go to the Home tab then go to the section of styles and click on conditional formatting then select highlight cell rules and go to text that contains once you click on it a drop down like this occurs and here it asks you that what sort of data do you think is required for which which sort of formatting will be done done you can go on and custom format anything you like but since I have the option in front of me for green fill with dark green text I'm going to select this one and we're good to go click on okay and the job is done now if the option is incomplete I want the color to turn red so I'm going to repeat the same process go to conditional formatting highlight cell rules go to text that contains and in here I want it to be filled righted which is perfectly fine I click on okay and this is done last option that we have left is in progress for in progress I want something yellowish or orangish so for that I go to conditional formatting again repeat the same series of steps but this time I'll select a yellowish option something like that and the job is done so now every time I click on complete the cell's going to turn green I click on incomplete the cell will turn red and the same way when I click in progress It's going to turn yellow bring about the same box for all of my options and the job is done so next time for Mr Assad I click on complete it's going to turn green for Miss Sandy incomplete reddish and for Mr Miss murf from Finance should be in progress and the job is done I hope you like the video and you have learned a lot from it this is very convenient do use this for making your status updates the easiest thank you very much see you in the next video hello everyone welcome to a new tutorial from Simon says it in this video tutorial I will show you how to merge Excel files Excel offers you the ability to combine data from multiple worksheets or workbooks into one single workbook which makes it easy for you to search for data decreases file clutter and facilitates easy sharing of files that in turn increases the efficiency of your work let us see how to merge Excel files from different workbooks into a single worksheet just by copying the data from the sheet into one book and pasting the data into another book you can consolidate all the data into a common workbook to copy all the data first select all the data from the worksheet right click on the selected data and click copy or press control+ C now open the workbook where you want to consolidate the data navigate to a new sheet and press controll plus v or click on the paste button from the home menu this way you can copy and paste any number of worksheets into different workbooks and consolidate the data into a single Excel file another method is to copy the sheets together and copy or move them to another workbook first open the workbook to which you want to merge all the files this will act as the destination then open the source workbook from which you want to move or copy to copy or move the books first select the sheets once you select the sheets they will appear in bold now right click and select move or copy this opens the move or copy dialogue box for from the two book dropdown select the workbook to which you want to move the sheets in the before sheet section select move to end additionally check the check box for create a copy if you want to create a copy of the selected sheets onto a new workbook this leaves the data in the source workbook intact click okay this will copy the selected worksheets onto a new workbook there might be some instances where you would want to merge Excel files into a single worksheet in such cases you can use the below mentioned methods to arrive at the desired result using power query you can merge Excel files into a single workbook and help alter the data with ease when using this method store all the files you want to combine into a single folder also take precautions to make sure the data are structured and in the same format as each other now open the Excel workbook in which you want to combine all the other workbooks navigate to the data tab under the get and transform data section click on the drop-down from get data click in from file and select from folder a dialogue box opens select the folder which contains the files you want to merge and click okay this inserts all the data from the selected folder and shows you a preview of the selected files click on the drop- down from Combine and select combine and load this in turn opens the combined files dialogue box from the sample file dropdown select any file and select any sheet under the display options parameters this file will act as a template based on which the data from other sheets will be formatted you can see the preview of the data in the course responding sheet check the check boox for skip files with errors if you want Power query to ignore the errors once you have verified the data click okay after you click okay the data from the workbooks are combined and reflected in a new sheet using the consolidate option in the data section you can combine multiple worksheets into one first open a new worksheet in a new workbook this will act as the destination where all the files will be merged navigate to data under the data tools section click on the consolidate button this opens the consolidate dialogue box since we will be adding data one after the other select some from the function dropdown in the references text box enter the cell range you want to consolidate and click on ADD you can add data from different work worksheets in different workbooks by using the up Arrow or by clicking on the browse button once you click on add the references will be added to the all references tab from here you can remove any references you don't want to consolidate using the delete button if you want the Consolidated data to be dynamic I.E the data in the destination changes when the source is changed click on the check box for create links to Source data finally click okay this consolidates the data from the selected Source references into a single sheet you can use the plus and minus buttons on the left of the sheet to view the data another effective way to merge Excel files is by changing their format and then reverting to their original format the first step is to convert the two Excel files into CSV format to do that open the Excel file navigate to file click on save as click on more options this opens the save as dialogue box select the storage location and rename the file under the save as type dropdown select the CSV comma delimited click on save this saves the excel files. xlsx in the CSV format the next step is to use the command prompt to merge files while using this method make sure you have placed the files you want to combine in the same folder now right click in the empty space and select open in terminal by default Windows opens Powershell editor click on the small dropdown next to the new tab or press contrl plus shift+ 2 to open the command prompt this opens the command prompt editor in the command prompt window enter the command CD space the folder path in double quotes press enter this shows that any command you enter is specified to the current folder enter the command in the prompt and press enter once the execution is complete you can see the names of the workbooks combined in the command prompt in the folder you can see the combined CSV file now you can convert the CSV file back to an Excel file that is all everyone in this video we saw how to merge files in Excel in five proven ways thank you hello everyone welcome to a new tutorial from Simon says it in this video tutorial I will show you how to reduce the size of Excel files when the data is added and its complexity increases the size of the Excel file also increases Excel files range from a few kilobytes to a few megabytes the huge amount of data associated with an Excel file tends to make it slow and hazy also a vast number of files with greater sizes make it very difficult to share in such cases you'll need to reduce the size of an Excel file let us now see how to reduce the Excel file size let us get right into it one way to reduce the Excel file size is by saving the Excel file is a binary spreadsheet to save the current file in binary format navigate to file click on on save as enter the file name and location finally select the Excel binary workbook option from the save as Pane and click on save this saves the Excel file as a binary workbook compressing images in Excel worksheet is another way to reduce the Excel file size to compress an image first select the image and navigate to the picture format main menu ribbon under the adjust section click on compress pictures this opens the compress pictures dialogue box you can select the resolution you want from the list of resolutions the higher the resolution the greater the size will be choose the resolution that suits your purpose or you can also select the used Default Resolution and click okay the resolution and editing factors play a major role in determining the size of the Excel file so by reducing or eliminating those factors we can reduce the Excel file size to reduce the image resolution and the file size navigate to file and click on options from the Excel options dialogue box click on Advanced scroll down to the image size and quality section from the dropdown select the workbook you want to implement the changes check the check box for discard editing data uncheck the check checkbox for do not compress images in the file change the default resolution when you select High Fidelity the images will have the highest resolution but they will significantly increase the file size if your spreadsheet has pivot tables then any changes you make to the pivot table get stored in the pivot table cache you can also reduce the file size by removing the pivot table cache to remove the pivot table cache first click on any cell in the pivot table then navigate to the pivot table analyze main menu ribbon under the pivot table section click on options click on the data tab in the pivot table options dialogue box under the pivot table data section uncheck the checkbox for Save Source data with file check the checkbox for refresh data when opening the file externally compressing a file also helps bring down the file size to compress a file right click on the file and select compress to zip file this instantly compresses the current file you can then share a large number of files easily and the end user can extract and get back to the original file watch is an efficient feature in Excel that lets you expect calculations formulas and data however enabling it takes up considerable space and makes the file size a bit large to remove the watches navigate to the formula main menu ribbon and click on the watch window this opens the watch window dialogue box in the dialogue box select the watches you want to delete and click on delete Watch Additionally you can also reduce the file size by removing any unnecessary data like images worksheets formatting formulas and hyperlinks that is all everyone in this video we saw how to reduce the Excel file size Excel files with lesser file sizes facilitate easy sharing and transfer of the files in addition they also load faster and are easier to download thank you in this lesson we're going to take a look at workbook protection and when I say workbook protection I'm really referring to three different elements protecting cells protecting worksheets and workbooks and protecting the workbook structure now let's start out by discussing why we would want to protect a workbook and if you're not familiar with protection all that means is that we're locking this workbook down so that edits can't be made for example you can see in the spreadsheet just here I have an example of an invoice and this is an invoice for the electronics company so we've got some items listed out here the quantity that's been purchased the shipping cost which is actually a calculation the price per item and then we have the line total now it might be that I don't want anybody to be able to edit these line totals just here which contain a formula but I do want them to be able to add more line items into this table so effectively what we would need to do there is lock the line total cells so that they can't be modified but keep everything else unlocked and this is what we refer to as protecting cells so let's take a look at that first of all now for the example that we've just spoken about there is one really important point you need to remember when you're working in an Excel worksheet every single cell is locked now you might think that's a little bit strange because we can click in any cell and we can start to type into it so if every cell is locked how can we type into cells well it's because that locking on the cells doesn't take effect until we we protect the workbook now if you don't believe me let's just click in a random cell and press control1 notice we have a protection tab up here and if you take a look at this it says locked we have a check mark in that box and you'll find that that is the same no matter what cell we click on in the worksheet so every cell is locked they just don't appear to be locked until we protect the worksheet so if I wanted to lock column e but I wanted to keep everything else editable all I would need to do is select the cells that I want people to be able to type into so I'm just going to select everything like that control1 to jump into format cells make sure you're on the protection Tab and we're going to unlock these cells let's click on okay now notice that as soon as I've done that I now have these little green warning triangles in this shipping column now if I click on the Little Triangle just here to take a look at this error it says unprotected formula so this is really just just a warning from Excel letting me know that these cells are no longer locked and there is a formula in this cell now in this case that's absolutely fine so I'm just going to select this entire column and we'll just say ignore error so now we have these cells unlocked these cells aren't locked at the moment because we need to protect the worksheet so let's jump up to the review Tab and notice in the protect group we have a protect sheet option so let's click this and this is where we can choose exactly what we want to lock we can also add a password up here if we want to so a password to unprotect the sheet so this is another level of security only people who have the password can actually access the worksheet now I'm going to leave the password blank and then if I wanted to I could choose to allow users of this worksheet to do specific things even though the worksheet is locked overall now I'm going to keep the two default options selected so even when this work worksheet is protected I'm still going to allow users to select locked cells they won't be able to edit them but they can click on them and I'm also going to allow them to select unlocked cells so let's click on okay so now what we should find is that if we go to this line total column I can click in cell E4 that's not a problem but if I double click to try and edit the cell I'm going to get an error because these cells are now locked I can however still type into the rest of the invoice so that is how you can protect specific cells in a worksheet now if you want to unprotect the worksheet it's simply a case of going up to unprotect sheet clicking it and that worksheet is now unprotected and we can edit every single cell in the worksheet now the second option that you have is to protect the entire workbook so if you have multiple sheets down here maybe you want to protect all of them remember worksheet sheets are contained within a workbook so we could click on the protect workbook button in the protect group on the ribbon and then we could enter a password we need to re-enter the password click on okay and now if we were to close this workbook down I'm going to go to file and close I'm going to say yes to save the changes now when we go to reopen it and I've got mine down here in my recent list you can see it's going to ask for a password so the entire workbook is essentially protected unless you have the password like so and of course if we go back to the review tab we can deselect protect workbook to unprotect it now another thing that you may not have noticed when we clicked on protect workbook is that we had this option selected protect workbook structure so what exactly does that mean well let's click on okay we're not going to add a password this time what that means is it's going to protect the actual structure of the workbook so whether somebody can do things like add new worksheets or delete worksheets because I've now protected the workbook structure if I rightclick on the invoice tab at the bottom notice that most of the options in this rightclick menu are grayed out so I can't insert a new worksheet I can't delete I can't rame I can't move or copy so maybe you just want to lock that down and protect the structure and not lock anything else down that's absolutely fine you don't have to add a password you can simply protect the structure now I'm going to unprotect this worksheet and when we right click we should find that those options are now back so those are the three different ways that you can protect cells in a worksheet the entire workbook and also the structure hello everyone welcome to a new tutorial from Simon Sayes it in this video I will show you how to create a waterfall chart in Excel one way to represent the positive and negative rise and fall of values in a table is by using the waterfall chart to create a waterfall chart using the data first select the data navigate to insert under the chart section click on the waterfall chart this populates the waterfall chart in the center of the sheet from the chart you can see that the increases and decreases in profit are indicated in the form of steps waterfalls or Bridges you can also make additional changes to the Chart as per your preferences when you populate a waterfall chart only the data at the beginning and the end starts from the axis to make the bars start from the xaxis doubleclick the particular data points to open the format data points pane to the right of the sheet under the series option check the checkbox for set as total this gives you the waterfall chart if if you are using older versions of Microsoft Excel you might not be able to directly populate the chart from the insert option in such cases you will have to create additional columns for the data points let us insert three columns in between the original values the idea behind creating three columns is that the base column will contain the data as an initial point the other two columns will contain the positive increasing values and negative decreasing values respectively first let us set up the decrease column from the sales flow column enter only the negative values in the decrease column and fill the rest of the places with zeros another easy way to separate data is to use the if formula and press enter now you can use the drag handle to apply the formula to the remaining cells in the same way enter the formula in the increase column and use the fill handle to apply the formula to the other cells next to fill the base column leave the first cell and enter the formula to add the base and increased value and subtract the decreased values this acts as the initial point now press enter and use the drag handle to fill and apply the formula to the remaining cells now that we have all the data in the columns ready let us now get ready to create the waterfall chart to create a waterfall chart select the data except for the sales flow column navigate to insert and insert the Stacked column chart this populates the chart in the center of the sheet now let us transform the Stacked column chart into a waterfall chart right click on any data and select format data series under the format data series pane click on the fill and line icon Under The Fill section click on no fill and under the Border section click on no line you change the chart Style from the shortcut customized buttons presented next to the chart or you can use the chart design ribbon to change the overall layout of the chart that is all everyone in this video we saw how to create a waterfall chart in Excel in two easy ways choose the method that suits your purpose the best thank you hello everyone welcome to a new tutorial from Simon says it in this video I'll show you how to use the match function in Excel Excel consists of a variety of functions that help in performing a variety of operations at ease if you want to find the position of a particular value when using Excel tables or pivot tables you can use the match function the main purpose of the match function is that this function is used to search any data in an array of cells and Returns the relative position of that particular data the syntax of the match function takes three arguments the lookup value the lookup array and the match type the lookup value is a mandatory field this argument denotes the value you want to search in the array the lookup array is also a mandatory field this denotes the data array or the cell range from which you want the function to look for the lookup value match type is an optional field and can take only the values -1 0 and 1 this field defines how you want the function to search the lookup value in the lookup array when the match type argument is zero the match function searches for and finds the first occurring value in the array exactly equal to the lookup value when the match type value is -1 the function finds the exact or the smallest value greater than or equal to the lookup value however when using this match type make sure the array is sorted in descending order when the match type is value one the match function finds the exact or largest value lesser than or equal to the lookup value however the values in the lookup array must be arranged in ascending order since this is an optional field the default value will be one even if you mention it in the formula or not let us now see how to use the match function in Excel with the match type zero to find the exact match for the particular Value First choose a destination cell enter the formula equals match and pass the AR arguments as the value cell range and the match type press enter this gives you the position of the search data that is all everyone the match function can be a very helpful tool that helps you find the relative position of the value this can help find replace or make any changes to the data in this video we saw how to use the match function in Excel along with its use cases thank you hello everyone welcome to a new tutorial from Simon say it in this video I'll show you how to insert a hyperlink in Excel hyperlinks are an important part of any application that you can use to access or refer to other data with just a click in Excel you can do much more than just hyperlinking a web page to the text you can embed other worksheets and workbooks create a new workbook and even create a hyperlink to an email additionally these hyperlinks not only pertain to text you can also add hyperlinks to charts and images in this video you'll learn how to insert a hyperlink in Excel in three easy ways the first method is by using the insert menu this is the most common method to insert a link into an Excel cell and it offers a variety of functionalities and options to insert a hyperlink first select a cell you can select any empty cell or a cell that already has text in them this text will act as an anchor text for the link after selecting a cell where you want to insert the link navigate to insert under the link section click link this opens up a new dialogue box in the insert hyperlink dialogue box under the link to section you can see options to insert a hyperlink for either a web page open a new workbook add a link to an existing worksheet or even a mail address select the option you want to insert the hyperlink to and enter the necessary details in this case let us hyperlink a web page in the address text box enter the correct address of the web page it is always better to practice to copy and paste the web address than to enter them manually if you haven't added any anchor text you can enter them in the text to display text box click okay this inserts the web page link into the cell which contains the anchor text when you click on the text you'll be directed to the respective web page another way to insert a hyperlink is by using a formula using the hyperlink function you can insert hyperlinks into cells in Excel and the hyperlink function takes two arguments to use the hyperlink function select a destination cell enter the formula in the destination cell and press enter in the same way you can also insert a hyperlink to a file just enter the file location in the first argument and the anchor text in the second argument this is an easy and simple way to insert a hyperlink in Excel just by dragging and dropping a cell you can insert a hyperlink to a cell first select a cell and enter the anchor text move the mouse pointer to the corner of the cell you can see the pointer turn to a foursided movable pointer right click and drag the cell to a new position or place it in the same position now leave the mouse button and click on create hyperlink here this hyperlinks the anchor text with the current workbook as default you can then copy and paste the hyperlink into a new worksheet or new workbook if you want to change the hyperlink rightclick on the link and click on edit hyperlink this in turn op opens the insert hyperlink dialogue box using this you can change the embedded link or file based on your choice to delete a hyperlink right click on the link and click on remove hyperlink that is all everyone we saw how to insert a hyperlink in Excel along with the options it offers you can either use the insert main menu and the insert hyperlink dialogue Box by using the formula or by dragging and dropping the cells thank you hi there everyone and welcome back to another lesson in this video we're going to learn how to visualize data with color scales color skills can be of tremendous help to you if you want to have a look at the data and get details in one CLS so let's jump in and see how this can be done as an example in front of me I have the names of the sell and the sales that did in dollars now if I want the color grading to be done in order to show me that who was the one who made the most sales and who was the one who made the Lesser sales I can use color scales for that the first step is to select all those cells which have the data that you want to be color scaled once that's done you can either click on control Q or click on control analysis box once you do that it gives you multiple options in the section of formatting your you're going to see an option of color scales once you click on it the job is done the one which is represented with red color is the smallest value or the smallest sale made and gradually you can see the colors are changing and the most dark green is showing Mr Placebo who had the most sales that's how it's done thanks for watching see you in the next video hi there everyone and welcome back to another lesson in this video we're going to learn how to visualize data using spark lines if you're looking for something minimalistic and something which does not take a lot of space on your Excel sheet you have got it right you got to use spark lines for showing the progress right in front of us we have an example of four tests that were taken for a number of students now if I have to have a graphical representation of all this data I can simply use use spark lines so let's jump into how this can be done so the first step is that you go to the section of progress or where you want the graphical representation of spark lines to appear once you're right there you got to select the whole row all those cells which you want to include in your spark line once you're done you can either click on control Q for the quick analysis or click on this icon and go to the spark lines once you go to it there are three different options whether to use a spark line column or win and losses in our case we're going to go with the line once you click on it now this is something very thin you can go to the section of designs and you can customize the color the width or the weight of the line too so I would like some we to be a little fatter so I can see it if the whole sheet is zoomed out once once this is done for one of them I'm going to drag it down for all of my students and here you go you can see the graphical representation which is very minimalistic and nice so let's say for Terrace I'm avoiding random marks for him and once I did you can see the whole graph is made and this is something really awesome to check out the progress instantly in one go I hope you like the video do try it out and that's pretty much it see you in the next lesson in the final lesson of this section we're just going to briefly discuss comments because comments can be a really useful way of leaving notes or helpful updates for other team members if you can imagine the scenario that we have this levers list shared with all of the members of our team and it might be that I want to leave a little note for somebody that this department needs to be updated because it's changed its name from creative to design now of course I could send them an email and let them know I could use teams maybe start a chat but a really nice quick way of doing this is simply to add a comment into the spreadsheet that everybody can see and in Excel 365 we have the new threaded comments system so let me show you how it works now if I want somebody to change the Department creative to design I could choose to select one cell I could select multiple cells to apply my comment to up on the insert ribbon we have a comments group so I'm going to click on comment and notice what it does here it opens up a little popup window you can see my name at the top so everybody knows that I'm the person who's making the comment and I can simply type in whatever I need it to say also notice that within threaded comments we can use at mentions as well as well so if there's a specific person on my team that is responsible for making these updates I could direct this to them and then they'll get a notification that they need to check out this spreadsheet and make a change so I'm going to say that I want a Dell I'm just going to start to type her name and if you are looking for people within your organization they should pop up in a list underneath let's select and I'm going to say to Adele please update the department from creative to to design now notice here we can press controll enter to post alternatively we can click on the little green paper plane Arrow to send that comment through so let's do controll enter and you can see what happens here because I've app mentioned somebody who currently doesn't have access to this spreadsheet it's asking me to share the spreadsheet with Adele and let her know that that's what I've done so I'm going to say yes share and notify and we will talk a lot more about sharing in later lessons but for now you can see that once you add a comment in you get this little purple tag in the top Corner if I hover my mouse over it I can see any comments that have been made and if I want to edit the comment I have a little edit comment button just here that will allow me to go in and make some changes or I can click the three dots and I can choose to resolve the thread or delete the thread now what do we mean by thread just here because these are threaded comments well anybody can come in here and they can reply to the comment so take a look what's happening here because I shared this workbook with Adele I can now see that Adele has arrived in the spreadsheet notice up in the top corner I can see Adele's little icon and as I hover over it's telling me that she is currently clicked in cell C4 so on her PC or on her mobile she's more than likely responding to the comment that I've just sent through and we can also see in the spreadsheet the cell that she's working on is highlighted in red and I can see her initials so this is kind of your introduction to co-authoring in EXL 365 if we share a workbook with other people we can all dive into that document and start to make changes so now let's see Adele has in fact responded she said no problem I'll do this shortly so I can now see that Adele has in fact made those changes and I can now see that Adele has also left the spreadsheet she's closed it down so this is what we refer to as threaded comments it just means you have this conversational style and you can at mention other people now once a comment is resolved effectively you can remove these comments simply by clicking the three dots and you can choose to resolve the thread or delete the thread so what is the difference between these two well if you want to keep track of what comments were made within this spreadsheet you would simply resolve the thread that's going to keep the comments in here but they're not active we don't require any action whereas if we delete the entire thread it's going to delete all of the comments and nobody would ever know that there were any comments in the workbook so I'm going to choose resolve thread we can see that's now been tagged as resolved but we still have that little purple icon in the top Corner indicating that there were comments in this cell and we can go in and we can see what those comments were so sometimes that can be really useful to keep those in even if the issue is resolved if you want to get rid of them entirely we can simply click on the delete thread icon in the top right hand corner and that's going to delete out all of those comments so that is how you can add comments to a workbook how you can reply to comments and it's a little bit of an introduction into collaboration in Excel 365 we're going to speak a lot more about that later but for the time being we're going to move on to the next section hello everyone welcome to a new tutorial from Simon says it in this video I'll show you how to change the margins in Excel when you print the sheets on paper setting the margins plays a pivotal role in determining the aesthetic of the page and data hence it's important to check adjust and change the margins depending on the data when you print so without further Ado let us see how to change the margins in Excel One method to change the margin is by using the page layout tab this is one of the well-known ways to change the layout of the sheet first navigate to the page layout tab in the main menu ribbon under the page setup section click on the drop down from margins you can see the current page margin is highlighted now you can click on the desired margin you want want to apply to the sheet once the particular margin is applied you can see the page break which appears with a dotted line when you navigate to the page layout tab and change the margin the setting only applies to the current sheet if you want to change the layout for multiple sheets hold the control or shift key to select non-adjacent or adjacent sheets respectively and then navigate to the page layout tab and then change the margin to apply to all the select sheets another way to change the layout of the sheets is from the file menu when you use this method you can see the preview of the margin changes and the selected margins only apply to the printed sheet and do not apply to the Excel sheet first open Excel and choose another particular sheet to change the layout navigate to the file main menu ribbon click on the print option on the print page click on the the drop down from margins and select the desired margin once you select the margin you can see the preview of the content with the margin applied in the preview pane click on print to print the sheet with the selected margin of your choice until now we have seen how to change the default margins in Excel however if you're not satisfied with it you can use the custom margins in Excel from the page layout tab you can use the custom margins option in two ways you can either navigate to the page layout tab click on the drop- down menu for margins and select custom margins or click on the page setup option which can be seen as an extended arrow in the page setup section this opens the page setup dialogue box in the dialogue box click on the margin section to customize the page margins based on your preference Additionally you can choose to Center the content either horizontally or vertically by using the checkbox finally you can see the preview print the pages with the current margins by clicking on print or apply the changes to the sheet by clicking okay another way to set up custom margins is by navigating to the file main menu and clicking on print from the print pane click on the show margins button in the bottom right corner this shows you the margin markers which can be seen as fine lines running across the page using this you can move and set the margins independently in addition to changing the top bottom right and left margins for headers and Footers you can also change the column width of the individual columns after specifying the margins you can click on print to print the pages that is all everyone in this video we saw how to change the margins in Excel you can choose the default margins or customize and and specify the margins based on your preferences thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to autofit data in Excel cells in Microsoft Excel there's a default row height and column width for all cells if you enter any text that is larger than a cell size it will spill out this is considered bad formatting and is unpleasant to work with to rectify this you can can use the Excel autofit feature there are three ways to use autofit in Excel you can use the mouse the Excel ribbon menu or directly use keyboard shortcuts to save time let us see them one after the other first let us see how to autofit using the mouse to autofit the column size of one column just hover the mouse over the right border of the column the double-headed Arrow pointer will appear now double click on the the column border to expand it if you want to apply the autofit feature to multiple columns or rows select all of them first now double click on any of the row or column borders inside the selection remember you can autofit rows columns or both at the same time depending on your needs another easy way to autofit data is by using Excel ribbons first select the relevant columns and click on the autofit column width button under the format dropdown menu the format dropdown menu is located in the cells group of the Home tab to autoit row height select the relevant rows and click on the autofit row height button under the format drop-down menu using the keyboard shortcut keys is relatively the easiest method to autofit excel cells first select the data either by holding the control key or the shift key next hit the ALT key this opens the hot Keys layout now press h this will select and open the Home tab in the ribbon then hit o this will select and open the format menu now to autofit column width use I in the same way autofit row height using the same way by pressing the keys alt h o and a that is all everyone in this video we saw how to autofit cells rows and columns thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to create an Excel gauge chart the Excel gauge chart also called the speedometer chart consists of a dial likee structure over which a needle-like pointer represents a specific point in the data this type of chart helps to depict the performance of the data we need to create three labels the first and second labels are used to create the donut chart and the final label is used to create the pie chart the first table denotes the category in the chart create the data points in increment values so that the intermediate data points can add to the maximum value the second table is optional it denotes the labels in the chart the third table denotes the pointer and three values in this table you can spe CL ify the value you want to denote and the width of the pointer the rest value denotes the total value of the first table minus the pointer value and the pointer width let us create a chart for the data in the first table navigate to insert and then click on the drop down from insert Pi or donut chart this will populate an empty chart since we have not selected any data to add data to the Chart right click click on the empty space and select select data in the select data source dialogue box click on add in the series name text box enter any name for the data in the series values text box click on the select data button and select the data under the value section of the first table click okay this gives you the dnut chart you can see the orientation of the chart is a bit off to change the angle of the chart right click on the donut chart and select format data Series in the format data series under the series options change the angle of the First Slice to 270° if you want to change the size of the chart change the doughnut hole size now let us delete the bottom part of the doughnut chart select the part you want to delete select the f fill and line section and click on no fill if you want to change the colors of the other data points click on the data points navigate to the line and fill section and select the color from the drop-down let us now select the data labels which will act as cues for the viewers in the customized shortcut buttons click on chart elements and select data labels this populates the numbers on the chart now right click on the numbers and select format data labels in the format data labels pane select values from cells and select the headers corresponding to the values click okay uncheck any other checkbox in the data labels option now delete any unnecessary data labels and elements by clicking on them thus we have the donnut chart which acts as a scale over which the pointer moves plotting the second chart by using the second table is an optional part of creating the Excel gauge chart this part is mainly helpful to give you in-depth cues and additional scaling to the existing chart add the secondary chart the same way you have added the primary chart then comes the third final and most important step in creating the Excel gauge chart to create the pointer rightclick on any empty space in the chart and select select data this opens up the select data source dialogue box in the legend entries series click on add this in turn opens the edit series dialogue box enter the series name in the text box given Below in the series values text box select the data in the values column from the third table click okay then right click on the donnut chart and select change series chart type type in the change chart type dialogue box select combo and click on custom combination in the pointer data series click on the dropdown and select Pi click okay if the angle is not correct right click and select format data series under the series option select angle of the First Slice to 270° now let us just leave the pointer in high the other parts of the chart click on the data points in the pie chart navigate to the fill in line section in the format data series and click on no fill delete any unwanted elements or move the chart elements to your preference and here it is we have successfully created the Excel gauge chart or the speedometer chart to our Lian when you change the pointer value in the third table the pointer in the dial changes according accordingly that is all everyone we saw how to create an Excel gauge chart in an easy and efficient way thank you if you are a PowerPoint user you might be aware of the eyedropper utility what the eyedropper utility allows us to do is basically use it to pick up a color anywhere in the PowerPoint slide and use it elsewhere so that's brilliant if you're trying to match colors or you're trying to use branded colors maybe you're trying to match a color from a specific logo you can literally click on it and transfer it to something else on the screen now unfortunately in Excel if I was trying to do that if I click on these bars and go to the format Tab and shape fill this is where you would find the ey dropper utility in PowerPoint in this little section down here but notice we don't have one in Excel there is a way by utilizing a little Windows feature that can allow you to do exactly the same same thing so I'm going to show you this example because I think this is really helpful so let's just say this is our company logo and it's over here and I want to color the bars in my chart to match one of the colors in this logo so everything looks cohesive and matches together so this is where we can use a little feature in Windows called power toys to do this now if you've never heard of power toys it is something you have to install onto your PC if you just gole Google power toys Microsoft the first link that comes up is going to take you through to this website and it's simply a case of clicking install power toys it's an official Windows thing so you're not downloading anything nasty and it's really cool it allows you to do lots and lots of different things it sort of extends the capabilities of your applications and of Windows and one of the things you can do with it is you can use you utilize an ey drop utility which we can then use in Excel so to bring up power toys we need to press alt space you can just search for power toys once you've installed it and it will bring it up it's this one just here now as I said there are lots of things you can do with power toys lots of things we're not going to get into any of these but one of them is the Color Picker now notice here the activation shortcut for Color Picker Windows key shift C so if I know this shortcut I don't even need to pull this up I can just in my Excel spreadsheet press window shift 6 and it turns my cursor into a Color Picker take a look at that and as I hover over colors in this logo it's telling me what the hexadecimal value is so if I have something very specific that I want to use so maybe this orange color I can just click there's the hex aimal value I can then copy it to the clipboard and I can then use it to color my chart bars so I can then click on my chart bars go to format shape fill your colors and this is where we can paste in that hexadecimal value really cool and you can see the last colors that you recently used are kept here as well so that's a really nice little trick because picking up branded colors can be a bit of a pain particularly if you need to use a specific color in your chart hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to remove comma in Excel commas are very common punctuation to separate text values in numerical 2 commas are used to separate unit places like hundreds or Millions however if you want to use any functions or formula the presence of commas can be a hindrance in this video I will tell you how to remove comma in Excel from both the numerals and text strings let us start with numerical values there are a couple of methods used to remove commas from numerical values in Excel One method is by changing ing the formatting of the cell to remove commas from the number first select the data with the cells navigate to home under the number section click on the number format option which can be seen as a small extend Arrow this opens up the format cells dialogue box in the format cells dialogue box click on the number section you can see the category of the cells is already selected as number if not change the category to number uncheck the checkbox or use 1,000 separator comma and click okay this changes the formatting of the cells and thereby removing any commas in between there are certain formulas you can use to remove commas from numerical values in Excel One such formula is the number value choose a destination to convert the values enter the formula in the destination set cell press enter this converts the value in the cell with a comma into a numerical value without a comma Now you can use the drag handle to apply the formula to other existing cells let us now see how to remove commas from text strings in Excel using the find a replace function in Excel you can easily remove the commas to remove the commas from the text values first select the cells with the text navigate to home under the editing section click on the dropdown from the find and select and select replace this opens the find and replace dialogue box click on the replace tab if it is not selected in the find what text box enter a comma then leave the replace with the dialogue box empty or just add a space click on replace all this replaces all the commas from the text in the cell and Excel throws a popup showing the number of places replaced click okay this method removes all the commas that appear in the selected cells and gives you the required values The Substitute formula is yet another formula to remove commas from text strings to remove commas from the text first select any destination cell enter the formula and press enter this instantly gives the output of the function that is the function Returns the selected data without any commas another easy way to remove commas from Excel text strings is by using the text to columns to remove the comma from the text first select the text navigate to data under the data tools section click on text to columns this opens up the text to columns wizard in the first step click on delimited and then click on next in the Second Step choose the delimiter in this case select comma you can see the preview of how the data would look in the data preview section click on next in the final step choose the format of the data column if you have no preference just click on finish once you click on finish you can see the data separated into different columns and the comma is removed that is all everyone we have seen five different methods to remove commas from both numerical values and text strings in Excel thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to change the row height or column width in Excel by default the rows and columns appear with specific height and width sometimes when you enter data in the cells the rows and columns might adjust to the height and width of the content let us see the five methods to change row height in Excel let's get right into it the first method is a prevalent and frequently used way to change row height or column width in Excel first identify the cell you want to change the height or width of if you want to change the row height place your cursor on the row headings on the left side of the sheet once you hover over the row or column headers you can see the mouse pointer change to a double-sided resize pointer Now by holding the left Mouse button drag to the desired height leave the mouse button this sets the height of the particular Row in the same way you can change the column width to get a perfect readable view there's also a very fast and easy method to change row height in Excel instead of altering the row height or column width this method sets the height or width to perfectly fit the content in the cell place the mouse pointer in between the rows where you want to change the height you can see the mouse pointer change to a double-sided resize pointer double click on the place and you can see the row height change to fit the content in the cell you can see that if the row or column is expanded double clicking on the row shorten it on the other hand if the row is shortened double clicking expands it in most cases when changing the row height you would aim to make the contents of the cell visible to make the content visible first select the cell you want to adjust the height navigate to home under the cell section click on the drop- down from format since we want to adjust the row height click on autofill row height this in instantly adjust the row height to fit the content of the cells this way you can adjust the row height for any number of cells all together also there might be times when you have to set the row height or column width to a specific value to specify the height or width select the rows that you want to change the height to do this navigate to the row header where you can see the mouse pointer change to an arrow left click and hold the mouse pointer to select the rows now right click on the row headings and select row height this opens the row height dialogue box select the row height you want and click okay this sets the row height to the specific units you have entered if you're not satisfied with the row height you can select the rows change the value again and click okay one advantage is that when you use this method and set the row height all the rows will have the same height making the spacing look aesthetically similar the final method is to use keyboard shortcuts using keyboard shortcuts also known as hotkeys you can change the row height in Excel select the rows you want to change height either by clicking on the left Mouse button and dragging them press the ALT key this opens up the hotkeys layout in the Excel Window now press h and then o you can see the keyboard shortcut keys which correspond to the specific options in the format dropdown if you want to set the row height to a specific value press h in case you want to autofit the row height to the content in the cells press a in the same way you can change the column width easily using the shortcut keys press the keys alt plus h plus o plus W one after the other to change the column width that that is all everyone changing the row height or column width is a very essential feature that provides the user with simplified data that is easy to read and understand in this video we saw how to change row height in Excel five easy ways using the same methods you can also change the column WID thank you than the next item I'll show you generates gasps of Amazement from my colleagues and peers this is called the concatenation function and it can be found in the functions Library so if I hit the formulas Tab and if I go in the text tab I have the concatenate function over here this function merges the contents of two cells into a single value so if we have a look at our spreadsheet we have the customer ID the customer name status and the revenue from January 2021 till December 2021 let's say I want to create a new column with the customer ID and customer name so basically I want to consolidate these two Fields now how do I do that so if I go in the text Tab and select the concatenate function I get a popup with the arguments so as mentioned I want to combine the data in the customer ID column with the data in the customer column but let's say I want to have a bit of a spacing between these two columns so I can select A4 then I can select a space and then I want to select the customer name as you can see when I select the arguments the solution gets automatically populated so this is very helpful as I can dynamically change the formulas so after one there is a space but let's say I also want to add a hyphen I can go in the text to argument category and also add a hyphen so this looks much better now there is a clear partition between the customer ID and the customer name so let me hit okay and the customer ID is now automatically populated now let's say I want to autofill the rest of the cells in this field as well so I can just go at the right hand side of the selected cell and double click and the data is now automatically populated and in this field so I have essentially created a new field using the existing data just imagine the amount of time we have saved by using the concatenate function in this data set there are over 200 rows of data and rather than manually going in and typing the customer ID and the customer name using a simple formula the data has been Consolidated within a minute or so now let's have a look at another useful function when we talk about data analytics we have just finished our fiscal year 2021 and everyone from the sales team has entered their accounts into a spreadsheet but no one did a tally of each status if I select this drop down I see that there are four statuses lever new customer old customer and with competitor so I have these four statuses and I want to for the four statuses we need to tally these numbers together so that we understand what happened throughout the year first of all I will create a new column and I'll call it total and then I will sum the data together so let me just do a sum of the entire year and I just drag the formula down make this a little bit wider and I'll also copy the formatting from the previous column and then on the right hand side just over here I want call the data by status so I have four statuses I have old customer I have new customer I have lever and then the fourth item was with competitor I'll just type in the data here with competitor me make this a bit bigger and then I'll call this column total so now I want the total per status so I can use a simple formula in order to achieve that the formula is called sum if so I'll start by typing equals sum if left parenthesis so I need to select the subset that is of Interest remember again I need to find the total by status I can select the entire section and I just drag it down using the Ctrl shift down button of my keyboard so that's the range that I need then I want to pick the criteria that is used to sum the data so the criteria is the status so for old customer it will be sell S4 and then I need to pick the sum range which is the total so I select column Q right parenthesis now before I hit the enter key please note that this data is dynamic so if I drag the formula down the numbers are going to change and I may not get the right calculation by status so I need to make this data static and as we discussed before you can easily do that by hitting the F4 key on your keyboard you'll have dollar signs that will appear this can remain Dynamic so as I drag the formula down the criteria cell address is going to change S4 will become S5 S6 and so forth so I can keep this Dynamic and then the sum range I also make it static by hitting that four key and now I press the enter key so the total of the old customer is $3.3 million I drag the formula down and now I have a tally of new customer lever and with competitor so if I were to summarize the company's performance in 2021 I can easily tell that the most of the revenue has come from the old customer and the company really needs to up its game because the revenue coming from new customer is significantly lower and the company even lost Revenue because some customers went with the competitor concatenation can help you consolidate various fields in the data set based on your preferences the summit formula allows you to do a quick tally based on whether the data set meets certain criteria and requirements both are very powerful tools for finding answers that regular filters cannot provide hi there everyone and welcome back to another lesson in this video we will be learning how to highlight checkboxes in Excel in the previous lesson we have already covered how to add and link checkboxes to certain cells so let's jump in and see how can we do this highlighting so the first thing is going to be selecting the cells which are having checkboxes in this case all of these checkboxes are unticked so I'm going to go and color them red if you want that every time you take the box and the statement changes to true the color of this certain cell should change to Green first of all go go to the cell where you want this changing to be applied go to conditional formatting new rule and click on user formula to determine which cells to format right over here we're going to go put our cursor go to the option of false and once the formula appears so it equals to true now go to format fill change the color to Green click on okay this time when I click on true you're going to see that the color of the box changed if I uncheck it it's going to change to fals again this way you can do the same changing to all of the rest of the cells so that whenever you check the cell it becomes true the color changes to green and when you uncheck all of the formatting is undone if you want the same formatting for all of the cells you will have to go to conditional form formatting manage rules and over here double click on the formula once you double click we're going to remove this dollar sign which is behind six the reason for doing this is so that every time the formula is applied we don't have the same cell locked and the cell positioning keeps changing as you go down the column we're going to click on okay over here we're going to select all of those cells where we want the certain formula to be applied click on apply go here select this worksheet and okay now every time I check or uncheck a box you're going to see that there is going to be the formatting happening separately thanks for watching hi everyone welcome to a new video from Simon says it in this video I'll show you how to bring back a missing scroll bar in Excel the scroll bar is a very important element in Excel we use it to move across the columns and rows without any hassle but what if one day it goes missing all of a sudden it's going to be difficult working without the scroll bars right and getting it back can be very frustrating well don't fret we're here to help you please watch till the end of the video to easily restore the missing scroll bar now let's get started without any further delay first of all there might be a a couple of reasons why the scroll bar goes missing in Excel all of a sudden the reasons could be one of these you might have accidentally disabled the scroll bar when you were exploring the settings section or you might have disabled it to increase the cell visibility another possibility is that you could have minimized the scroll bar there is an option right below the scroll bar which you might have accidentally clicked on it one more reason could be that the scroll bar is simply out of view that is because when you zoom in or minimize the Excel Window the scroll bar would also automatically hide or Shrink now that you know the reasons why the scroll bar is missing let's see how to troubleshoot it and fix the missing scroll bar issue let's see them one by one fix One enable the Excel scrollbar let's see how to enable the Excel scroll bar option to do that go to the file Tab and click on options on the left hand side click on the advance section on the right hand side scroll down and locate the display option for the workbook section under it click on both show horizontal scroll bar and show vertical scroll bar options click the okay button to save the changes fix two maximize the scroll bar sometimes you have the scroll bar option enabled but still the scroll bar goes missing there is a chance that it is minimized and you need to maximize it now do this step to restore the scroll bar back at the bottom on the right side of the page you can see see three small dots click on that and drag it to the left side to show the scroll bar and now check and see if you can see your scroll bar is now enabled fix three arrange the tiles in some versions of excel a scroll bar might be missing if the tiles open Windows aren't arranged properly let's see how to fix this first click on The View tab then click on the arrange all option select the tiled option and click on okay to save your chn changes this will fix the missing scroll bar issue if it is caused by improperly arranged tiles fix four resize the Excel Window when the Excel Window is minimized or if the size is altered the scroll bar gets hidden you have to resize the window to make the scroll bar appear now let's see how to do this click on the files name bar at the top of the window and drag it to adjust the view drag it until the edges of the Excel Window are visible to you click on the maximize button near the close button now check whether the scroll bar has appeared on the spreadsheet That's all folks I have discussed the four easy ways to fix the missing scroll bar if you have any questions about this please feel free to ask in the comment section we are always happy to help if you want more videos like this please hit the Subscribe button thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to remove tables in Excel when you create a table with the data Excel automatically adds some formatting of its own however in some cases you might need to remove the table formatting or even the whole table let us first see how to remove the table formatting in Excel to remove the table formatting first select the table navigate to Table design and under the table style section click on the more dropdown scroll down and click on clear from the dropdown this only removes the formatting of the table but the data filters and other elements remain in the table format Now navigate to home under the editing section click on the drop down from the clear and select clear formats this removes the dropdown from the headers and converts the table to normal range data in the above mentioned method we saw how to remove the formatting in a table however in some cases you might have to delete or remove the whole table if there is no need for an Excel table you can easily convert a table to normal data using this method first select the Excel table that you want to convert to normal data navigate to Table design under the tools section click on convert to range you can also right click on the table and click on the extend option from table and select convert to range Excel throws a warning popup asking do you want to convert the table to a normal range click yes this instantly converts the table to normal data you can also see that though the table is removed the formatting style remains so before you convert the table to a normal data range it's always better to remove the table formatting using the above method and then remove the table there's another easy way to remove the formatting in the cell first select the cells you want to remove the formatting from then navigate to home under the editing section click on the drop- down from clear and select clear formats this deletes the formatting of the selected cells and turns the data back to the default formatting Additionally you can also use the clear button to remove tables in Excel this method acts as a hard reset option this method can be used when you want to clear the formatting of the cells in addition to the data in the table to clear the table along with the data first select the table navigate to home under the editing section click on the drop-down clear and select clear all this removes the table including the data and Returns the blank cells that is all everyone Excel offers you the flexibility to change or even remove the formatting of the table to suit your needs Additionally you can can also delete the whole table depending on your choice in this video we saw how to remove tables and formatting in Excel in an easy way thank you hello everyone welcome to a new tutorial from Simon says it in this video I will show you how to switch between sheets in Excel when you are working in Excel there will be a need for you to switch between sheets in Excel here are six effective methods you can use to switch between sheets in Excel the first method is one one of the most well-known and widely used methods to change worksheets in Excel to change the worksheets place your mouse pointer in the worksheet tab which lies above the status bar once you place your pointer the particular sheet will appear bold when you click on it the current worksheet will be opened this is known as the active sheet if you have more number of sheets some sheets may be hidden to show the hidden sheets click on the Arrow next to the first displayed sheet and then select the required sheet if you're a person who uses a keyboard for efficient functioning then there is a keyboard shortcut to switch between sheets and Excel easily if you want to move to the next worksheet I.E to your right in the Excel workbook hold the control key and press the page down key to keep moving right press the page down key until you reach your desired sheet to move to the to the previous worksheet I.E to your left hold the control key and press the page up key on the keyboard this is also another way to switch between sheets in Excel using the go-to option you can move to any particular cell in any sheet to switch between sheets using the goto option first navigate to Home enter the editing section click on the drop- down from find and select and select go to this opens the goto dialogue box in the reference text box enter the sheet name followed by a separator exclamation point and the cell number once you press okay Excel takes you to the corresponding sheet and highlights that particular cell another easy way to move between sheets in Excel or move to a specific sheet is by using the active sheet option to switch between sheets using the active sheet option hover your mouse pointer over the arrows used to navigate between visible sheets this can be found in the bottom left corner of the Excel Window above the status bar you can see a popup notification rightclick in between the arrows this opens the active sheet dialogue box select the sheet you want to move and click okay or double click on the sheet you want to move you can also use the arrow keys to navigate between sheets and press enter to move to the particular sheet another pretty simple way to move between sheets is by using the name box in the Excel Window switching sheets using the name box works on the same Principle as the go-to method to switch between sheets using the name box first click on the name box enter the sheet name separator and sell number in the name box this immediately switches to the sheet and highlights the specified C hyperlinking is another way to switch to a different worksheet with just the click of a button this is helpful when you have a main reference sheet to switch to to add a hyperlink to a particular worksheet first select the cell navigate to insert under the link section click on the link button you can also right click on the cell and click on link this opens the insert hyperlink dialogue box click on the place in this document section and select the sheet you want to move to click okay if you want to have a different anchor rather than a default one change the text in the text to display text box this inserts the anchor text containing the hyperlink in the specified cell now when you click on the anchor text Excel takes you to the specified worksheet that is all everyone in this video we saw six easy ways to move between sheets in Excel thank you if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to see the full course that this video came from click over there and click over there to see more videos from Simon says it

Channel: Simon Sez IT

Views: 25,921

Keywords: excel tips and tricks, excel hacks and tricks, excel tricks and tips, best excel tricks, excel tutoring, microsoft excel tips, microsoft excel, excel tricks, excel tutorial, excel, excel tips and tricks 2022, microsoft excel tutorial, microsoft excel training, microsoft excel for beginners, excel training, how to use excel, excel tutorial for beginners, how to learn excel, excel tutorials, excel for beginners, excel tips, ms excel, excel tips and tricks you must know

Id: JPzfno4ot-g

Length: 127min 11sec (7631 seconds)

Published: Tue Jan 09 2024