Ten Excel Tips that are Easy to Learn - Part 3

Hello everyone, welcome to a new tutorial from Simon Says It. The Excel Freeze Rows feature is one of the most important tools to manage how your spreadsheet appears when you are working on it. Its main objective is to freeze a row or set of rows in view so that you can easily view different parts of the spreadsheet at the same time. In this video, I'll show you four easy ways to freeze rows in Excel. First, let us see how to freeze a row in Excel. Scroll up or down until you see the desired row appears at the top of your screen. Go to the View tab and click on the Freeze Panes drop-down menu. Under the drop-down, choose Freeze Top Row. This will freeze the topmost visible row of the spreadsheet. The procedure to freeze columns is the same as freezing the row. To freeze a column, scroll left and right until the desired column appears first at the left of the screen. Now click on the Freeze First Column option from the drop-down list. This will freeze the first visible column of the spreadsheet. Additionally, you can also freeze panes in your spreadsheet. To freeze panes in your Excel sheet, select a cell. Then navigate to View from the Freeze Pane drop-down and click on Freeze Panes. The pane freezes to the left and above the cell. Now that we have seen how to freeze rows and columns in Excel, let us see how to unfreeze them. In case you want to unfreeze any frozen column or row, just go to the View tab. Then click on the Freeze Panes and click on Unfreeze Panes option. This will unfreeze all locked rows and columns and your spreadsheet will be back to its original state. That's all folks. Freezing panes, rows, and columns and unfreezing them is so simple. Hope you liked it. Suppose you have any queries, feel free to leave them in the comment section below. We are always happy to help you. Thank you. Hello everyone, welcome to a new tutorial from Simon Says It. In this video, I will show you how to convert Excel to Word using two easy methods. In some cases, the Excel document may not be compatible with other software, or you might need to update or override any data. In such cases, you will have to convert an Excel file to a Word file. Let us jump right into the video without further ado. First, let us see how to convert an Excel to Word using the copy and paste method. This is an easy and simple way to convert an Excel file to a Word file. That is by copying contents from the Excel file and pasting it into a Word file. First, select the content to copy. Then, right-click the selected content and click on Copy. Or you can use the keyboard shortcut Control plus C to copy the contents. The copied content appears with a moving dots animation. Now open an empty or existing Word document. Right-click on the destination and click on Paste. Or you can use the keyboard shortcut Control plus V while right-clicking on the destination cell to paste the contents. The paste function has some options enabled to it. They help in choosing our preferences and formatting while copying the content to the Word file. This copies the data from the Excel sheet to the Word document. You can now save the file as a Word document either by clicking on Control plus S or by navigating to File and then clicking on Save. Then, choose the destination and save the Word file. Another easy method to convert an Excel file to a Word file is by using the insert method. First, open a Word document. Click on Insert in the menu bar. Under Text, click on Object. This opens up a dialog box. Click on Create from File. Check the link to the File checkbox to automatically update the data in the Word file. That is, if any changes are made to the original Excel file, the same changes will get reflected in the Word document. Choose the Excel document to be converted to a Word file and click on OK. This inserts and links the Excel file into the Word document. Now you can save the file as a Word document. Go to File and click on Save and save the file or click on Control plus S to save the file on your device. That is all folks. In this video, we saw two easy ways to convert content from an Excel file into a Word file. Use the copy and paste method to choose the formatting of the copied text and use the insert method to insert the Excel file onto the Word file. Choose the method which suits you best. Feel free to leave your queries regarding Excel in the comments below. We are very happy to help you out. Thanks. Hi, everyone. Welcome to a new video from Simon Says It. In Excel, numbers play a very major role. In some cases, you might need to round off the numbers, sometimes it is unnecessary to round off values. But Excel automatically rounds off values greater than 15 digits. So, what do you do? In this video, I will show you how to stop Excel from rounding the values using four easy methods. The first method is to change the format of the cells. In Excel, the default cell formatting is General. You need to convert the format to Number so it can show more digits. To change the cell formatting from General to Number, select the cell you want to change the format. Go to Home. And in the Number section, from the dropdown, select Number. You can also use the keyboard shortcut Ctrl plus 1 to change the formatting of the cells. Select Number in the Format Cells dialog box. You can also specify the decimal places and significant digits and click OK. Now you can see the format of the cell changed and the value exhibited as a complete number. The second method is to change the decimal places. To change the decimal place, select the cell with the rounded value. Navigate to Home. In the Number section, click on the Increase Decimal option to extend the significant digits which show up in the Excel sheets. This option gives you the liberty to choose the number of digits you want to display. In some cases, denoting numbers consisting of more than 15 digits is necessary. Values like bank balances or telephone numbers might not be displayed in Excel. To overcome this, you can add an apostrophe in front of the number. This converts the number to a string. Please keep in mind, Excel only has a display restriction for numbers, whereas a string does not have any restrictions. Adding an apostrophe is the appropriate workaround for you. Another reason Excel was not able to display the full range might be because of the width of the column. To overcome this error, you just have to extend the Column tab. Place the mouse pointer in the Column header. You can see the mouse pointer change to a double-sided pointer. Click on the column and drag the column to your desired width. In this video, we saw four easy ways to stop Excel from rounding. You can stop Excel from rounding the values by changing the format of the cells, by changing the decimal places, adding apostrophe, or extending the width of the column. Choose the method which suits you best. If you have any queries, please leave your thoughts in the comment section below. We are always here to help. Thank you. Hello everyone. Welcome to a new tutorial from Simon Says It. In this video, I will show you how to calculate subtotal in Excel. Subtotal is a function in Excel that helps you to calculate the average, sum, product, and other functions for the elements in the table. In this article, I will show you how to use the subtotal function in Excel in two methods. First, let us see how to use the subtotal function from the main menu. Select the cells of the table. Right-click and select Sort. You can select Ascending or Descending depending on your preference. Once the changed values are grouped together, we can now calculate the subtotal. Navigate to Data. Under Outline, click on Subtotal. This opens up a new subtotal dialog box. In the At Each Change In drop-down, select the option which you have grouped together or categorized in the rows. This is where the subtotal function performs on similar data. The second option is the Use function. We know that the subtotal function houses a variety of operations like sum, count, average, product, etc. You can choose your preferred operation from the drop-down. Finally, in the Add Subtotal To drop-down, select the column you want to function the act on. Now click OK. This gives you the subtotal and the grand total for the selected value. While projecting data, you might not want an extensive report containing all the elements. In such cases, you can use the 1, 2, 3 buttons or plus-minus symbols to show or hide the data. Another way to arrive at subtotal in Excel is by using a formula. Enter the formula in the destination cell. Once you enter the formula, Excel automatically shows the suggestions for the operations that you want to perform on the data. From the drop-down, double-click or select the preferred function and press the Tab key. This adds the function argument you want to perform on the data. While entering the operating parameter for the subtotal formula, you might wonder why there are two sets of operations with the same name. The first set of operations 1 through 11 is used to calculate the values presented in the table even if they are visible or hidden. The second set of operations 101 through 111 is used to calculate any visible values. They ignore the values that have been hidden manually. Now for the range argument. You can either enter the range or select the range and it will get populated. Press Enter. This gives you the subtotal of the data in the table. That is all, everyone. In this video, we saw how to calculate subtotal in Excel using two easy methods. If you have any queries, please leave your thoughts in the comment section below. We are always here to help you. Thank you. Hello, everyone. Welcome to a new tutorial from Simon Says It. In this video, I will show you how to create a slicer in Excel. Excel is usually preferred for its ability to store and access large amounts of information. To improve user friendliness, Excel has a lot of options and functions available to help the user search, find, or retrieve data. One among them is the Excel Slicer option. Excel Slicer works like a filter, helping sort out information from pivot tables and Excel tables. They help filter out and point out the required information from the large repository of data and present them in an easy to read and appealing format. In this guide, I will show you how to create a slicer in Excel and how they work. There are two ways to create an Excel slicer. One from the pivot table and the other from Excel table. First, let us see how to create an Excel slicer from a pivot table. When you have a pivot table in Excel, you can create the Excel slicer using the Pivot Table Analyze option. Under Filter, click on Insert Slicer. This opens up an Insert Slicers dialog box. In the dialog box, select the elements to create slices of. You can choose one or more slices. Click OK. This creates the slices. Now, you can sort the required data from the large repository of data easily. Second, let us see how to create an Excel slicer from an Excel table. To create a slice from an Excel table, go to the Insert menu. Under Filters, click on Slicer. You can also select the Insert Slicer from the Table Design menu under the Tools section. This opens up an Insert Slicers dialog box. Select the elements you want to create slices for. You can also select multiple parameters while slicing elements from the table. For example, if you want to know about the students from two cities, Buffalo and Brunswick, you can use the multi-select option to select two or more parameters from the city slice. You can clear the parameters using the Clear Filter option to clear all the parameters and show all the elements in the original table. This creates the slices for the Excel table where you can sort out data easily. You can customize the slice parameters using the Slicer menu in the menu bar. Slicers in Excel are a great way to retrieve important information from a large set of data, resulting in lesser manual work and providing maximum efficiency. In this video, we have seen how to create an Excel Slicer and how they work. Use them in the right places to ease your effort and make the data more presentable. If you have any queries, please leave your thoughts in the comments section below. We are always here to help you. Thank you. Hello everyone, welcome to a new tutorial from Simon Says It. In this video, I will show you how to graph a function in Excel. Excel has proven to be a beneficial tool for performing various calculations. In addition to calculating the values, Excel also has the ability to provide a relationship between the input and output values in the form of graphs and charts. In this video, I will show you how to use a function and graph a function in Excel using two easy ways. Let's dive into it. In Excel, you can use the functions to establish a relationship between the input and output easily. There are two ways to use functions in Excel. First, if you are going to be operating on any mainstream function, Excel has a huge library of built-in functions you can choose from. You can just select the functions, enter the values, and Excel gives you the output. Select a cell and enter the values. Now, to add a function, click on any destination cell. Navigate to Formulas in the menu bar. Under Function Library, you will find a variety of categories. Select the function you want to operate on. This opens up another dialog box asking you to enter the arguments for the function. You can enter any constant value. Or if you want to select or add the name of the cell in the text box and click OK. This gives the sine wave value for the given input. Now you can use the drag handle to perform the function on other cells too. Secondly, there are some functions that might not be available in the Excel function library. In such cases, you can manually create a function and get the output. Manually entering the function in Excel is very simple. Just add an equal sign before the function in the destination cell and type them. In the case of variables, select or enter the cell number to get the output. Consider the example of a quadratic equation. Enter the inputs in one column. Let it be called X. The inputs can be positive, decimal, negative, or even zero. Now enter the function in the destination cell. Always remember to add an asterisk in place of multiplication when entering functions manually. Press enter. This gives you the output corresponding to the function and the input. You can use the drag handle to add the function to other cells and get a series of outputs. Once you have the input and output for the required function, it is fairly easy to plot a graph. To plot a graph, select the X-axis input and Y-axis output of the graph. Go to the Insert menu. Under Charts, select Scatter. Click on the type of scatter chart to represent the data. This plots a graph for the function with the inputs and outputs along the X and Y-axis respectively. That is all, everyone. Plotting a function in the form of a graph gives an insight into the usage of the function easily to interpret the data. In this video, we saw how to use a function and graph a function in Excel. If you have any queries, please leave your thoughts in the comment section below. We are always here to help you. Thank you. Hello, everyone. Welcome to a new tutorial from Simon Says It. While using Excel, you will be dealing with lots of data in text and number formats. Sometimes text and numbers may get accidentally intertwined, making it hard for Excel to dereferentiate the numbers from the text. This causes some errors and issues when using functions like sum and round since they only work on numbers. Excel throws an error in case of such exceptions. To avoid this, you need to convert the numbers formatted as text back to numbers. In this video, I will show you how to convert text to numbers in Excel in five easy ways. First, let us see how to convert text to number using the drop-down button. When a number is indicated as a text, it can be seen with a green flag towards the left top of the cell along with an exclamation mark notification. If you see this, you can easily convert a text to a number by clicking on the drop-down to the right of the cell. Click on the drop-down and select Convert to Number. When a text is converted to a number, the values appear to the right of the cell. Next, let's see how to convert text to number using the Text to Column option. Select the column which contains the numbers as text. Click on Data and select Text to Column. Click on Finish. This converts your text to numbers. Another easy method to convert text to numbers is by using the Paste option. Enter 1 in any of the cells. Right-click on the cell and click Copy or you can use the keyboard shortcut by pressing Control plus C. Now, select the cell. Right-click on the cell and select Paste Special. This opens up a dialog box. In the Paste Special window, select All and select Multiply. Click OK. Since we are multiplying the text by 1, the text gets converted to a number and returns the same value. You can also convert a text to a number by using certain functions together. Enter the function in the cell where you want the text to be converted as a number and enter the cell number in the parentheses. This converts the number into a new cell. You can use the drag handle to apply the function to other cells. Now, once they are converted, you can copy the formatted numbers and paste them to their dedicated places by clicking on Paste Special and selecting values. By using mathematical operations on a text, you can convert text to numbers. You can operate with 0 while adding and operate with 1 while multiplying or dividing. To convert a text to a number using arithmetic operations, enter the arithmetic operation in the destination cell. For example, to convert the text to a number, multiplying any value by 1 gives the same value. This way, the values get converted from text to numbers. Knowing how to convert text to numbers in Excel is essential for using some functions in Excel. Hope you learned how to convert text to numbers in Excel in 5 easy ways. Please leave your thoughts or queries in the comments section below. We are always happy to help you. Thank you. Hello everyone. Welcome to a new tutorial from Simon Says It. Copying and pasting data are two commonly used operations while working on any application. Copying data eliminates the need to create or enter the data again manually. In Excel, you can copy data between cells, sheets, and even workbooks. However, when working on large data, you might only need to copy data that is visible or that has been filtered. In this video, I will show you how to copy visible cells only in Excel using 3 easy methods. One way to copy visible cells only in Excel is by using the Go To Special option from the Home menu. To copy only the visible cells, select the cells you want to copy. Navigate to Home. Under Editing, click on the drop-down from Find and Select. Select the Go To Special option from the drop-down. This opens up a Go To Special dialog box. From the dialog box, select Visible Cells Only and click OK. This option only copies the cells that are visible and ignores any hidden or filtered cells. If you look closely at the cells, you can see the cells are separated by a fine gray line. This denotes that any operation made will pertain only to the visible cells. Now, as usual, you can copy the cells. You can either right-click on the cells and select Copy or use the keyboard shortcut Control plus C. You can see the selected cells appearing with the moving dots animation. Select the destination cells and paste the copied data. You can either right-click on the cell and select Paste or use any of the paste options or you can use the keyboard shortcut Control plus V. This is another easy way to copy visible cells in Excel. This method will be most useful if you have to deal with more filters and values. You can easily toggle this option to copy visible cells whenever you want. To enable the Visible Cells Only option in the Quick Access Toolbar, open your Excel spreadsheet. Click on the small drop-down towards the top right of the menu pane called Ribbon Display option. You can access this drop-down from any main menu option. From the drop-down, click on Show Quick Access Toolbar. This shows another toolbar below the main menu pane. Click on the drop-down on the left of the Quick Access Toolbar and click on More Commands. This opens up an Excel Options dialog box. Under Choose Commands from drop-down, click on All Commands. This shows all the options to add to the Quick Access Toolbar. It'd be easier to find select visible cells since the buttons are arranged alphabetically. Click on Add. This shows the selected button in the previous pane. Click OK. This adds the Select Visible Cells button on the Quick Access Toolbar. Now follow the same steps to copy and paste as mentioned in the above method. To copy the visible cells, first select the cells. Now click on the Select Visible Cells button from the Quick Access Toolbar. Now copy the cells and paste them into their destination. You can see only the visible cells are copied and pasted. We know shortcut keys greatly help in simplifying the work which requires additional steps. To copy only the visible cells using shortcut keys, select the data first. Now press Alt plus semicolon. All the operation you perform after pressing Alt plus semicolon only pertains to the visible cells. You can see the borders of the selected cells displayed in gray. You can now use the shortcut key Control plus C to copy the selected cells. The copied cells appear with a moving dots animation. Now select the destination cell and paste the data. You can either right-click the destination cell and select paste or use the keyboard shortcut Control plus V to paste the data. This copies only the visible cells to the destination cell. The option to copy only visible cells is very useful to segregate and sort out data when large data are involved. In this video, we saw how to copy visible cells only in Excel using three easy ways. You can use the method which suits your purpose. If you have any queries, please leave your thoughts in the comment section below. We are always happy to help you. Thank you. Hello everyone, welcome to a new tutorial from Simon Says It. Sometimes, more than one chart or a combination of data in a single area might be needed to depict the data in the most effective way. In such case, we can use the secondary axis to represent two various data points in a single chart. In this video, I will show you how to add a secondary axis in Excel. This is an easy method to create a graph or chart with data from different units. Depending on your selected data, Excel offers you suggestions of charts to plot the data. To plot the data in the form of a graph, first select the data. Navigate to Insert. Under Charts, click on Recommended Charts. Depending on the data you selected, Excel offers you recommendations of charts. Select one that suits your purpose best. Clicking on Recommended Charts opens up an Insert Chart dialog box. Click on your preferred choice of the chart, and the description of the chart appears with its use cases. Click OK. The chart gets populated in the middle of the Excel sheet. Once the chart is populated, you can now customize the chart to your liking. Now let us see another method to add a secondary axis in Excel manually. First, select the necessary data. Navigate to Insert. Under Charts, select any of the charts you want to use to represent the data. Once you click on the chart, it populates in the center of the screen. From the chart, you can see, along with the sales made, the profit is also plotted with the same scale. In case the data is too small to select, click on the larger data and press the Tab key. To right-click on the secondary data, hover the mouse over the data. Then the mouse turns into a four-sided pointer. Now right-click and select the Format Data Series option. This opens up the Format pane on the right side of the sheet. In the Format pane, under Series Options, select Secondary Axis. To change the secondary data into the preferred chart type, right-click on the secondary data and click on Change Series Chart Type. This opens up a Change Chart Type dialog box. This dialog box provides a variety of options that enables you to create, customize, and combine any chart independently. To change the chart type of the secondary axis, click on the drop-down from Series 2. This shows you different types of charts to choose from. Select the one which is most preferred. Click OK. This adds the necessary secondary axis to the existing chart. That is all, everyone. In this video, we saw how to add secondary axis in Excel in two ways. You can choose any method which suits your purpose. Hope you liked it. Suppose you have any queries. Feel free to leave them in the comment section below. We are always happy to help you. Thank you. Hello, everyone. Welcome to a new tutorial from Simon Says It. In Excel, we need to select cells to perform certain operations or to use functions. In those cases, we either select whole rows, columns, individual cells, or a group of cells. What would you do if the cells are far from each other and you need to select them? In this video, I will show you five ways to select non-adjacent cells in Excel. Let's start with using the Control key to select the cells. Holding the Control key and selecting the cells is one of the best ways to select non-adjacent cells in Excel and this approach is pretty straightforward. By pressing and holding the Control key, select the cells you want to select. You can either click on each cell or use the mouse to drag to select a range of cells. Once you have selected all necessary cells, leave the Control key. In some cases, you might have to use only the keyboard and perform certain operations. When confronted with that situation, you can use the function keys to select the non-adjacent cells. To select the non-adjacent cells using function keys, click on any cell to act as an active cell. Now, press the function key F8. This enables the extended selection mode. You can see the extended selection mode enabled from the bottom left of the spreadsheet near the accessibility status. To select the non-adjacent cells, press Shift plus F8. This in turn enables the add or remove selection mode. Now, use the arrow keys to move the selection and select the cells you want. Once you have moved to another non-adjacent cell, press F8 again to enable the extended selection mode and select a cell or group of cells. After you have selected the non-adjacent cells, press F8 to get back to normal mode. Another easy way to select non-adjacent cells in Excel is by using their cell names to make the selection. To select the cells, first click on any cell. This marks the cell as the active cell. Now, click on the name box in the top left-hand corner of the spreadsheet. Type the name of the cells separated by a comma in the name box. You can enter the name of the single cells or the range of cells separated by a comma. Press Enter. This selects all the non-adjacent cells in the spreadsheet. Another way to select non-adjacent cells is by using the Go To option. This way of selecting non-adjacent cells is similar to selecting cells using the name box option. To select non-adjacent cells using the Go To option, navigate to Home. Under Editing, click on the dropdown from Find and Select and click on Go To. Or, use the keyboard shortcut Ctrl plus H. This opens up a Go To dialog box. Enter the name of the cells or a range of cells separated by a comma and click on OK. This selects the non-adjacent cells in the Excel spreadsheet. There is another special case to select non-adjacent cells in Excel. However, this method can be used if the content of the cells is similar. To find cells with similar values, navigate to Home. Under Editing, click on the dropdown from Find and Select and click on Find. Or, you can use the keyboard shortcut Ctrl plus F. This opens up a Find and Replace dialog box. Click Find and enter the value you want to locate. Click on Find All. This shows the location and description of the cells with the specific value. Click on Ctrl plus A to select all the cells with similar values. Now, either click on Close or the X button to close the Find and Replace dialog box and keep all the selected cells. In this video, we saw how to select non-adjacent cells in Excel using five methods. You can choose any method which suits your purpose. Hope you liked it. If you have any queries, feel free to leave them in the comment section below.