As the world’s most popular spreadsheet, Microsoft Excel has a wealth of features that allow its users to perform a wide variety of calculations and other functions. Below we provide some tips to help you master this powerful solution:
Typing groups of numbers such as dates can be tedious; the autofill function can help speed the process along. To use it, begin typing a series and place the cursor on the lower right part of the last cell: this is known as the fill handle. Once this changes to a plus sign (+), you can click and then drag downwards to choose all the cells you want to fill. The pattern you started will be filled in.
This can work up a column or to the left or right on a row also. In addition, auto filling can work by picking a cell or multiple cells and moving to the fill handle and clicking and dragging. This brings up a menu with options. When more data is inputted initially, the Fill Series will be better at generating AutoFill options for you to use. This Microsoft tutorial offers more information about the process.
2. Using Text to Columns
This function is helpful for tasks like moving names in a single column, such as first and last names, into two columns for various purposes. If you have a set of names with first names followed by last in a single column, select the ones you want and then click Text to Columns on the Data tab. After that select the method to use when separating them, either via delimiters utilizing spaces or commas or using a fixed width. Extra options exist for certain numbers. Use fixed width when all the data you want to view is located in the first column, while being separated by either a fixed amount of spaces or a period.
PivotTables are summaries of data that are sorted based on reference points you select. For instance, a list of goals scored by all the players for every team in a football league each month of the season could be reduced to a single player per month. These tables are useful for dealing with and analysing big data sets.
To create one, make sure that all the columns and rows are properly titled, then choose PivotTable on the Insert tab. You can also select the Recommended PivotTables option to find out if Excel can choose the right table for you. Another option is to use the PivotChart, which generates a PivotTable with a graph for easier comprehension.
4. Using Paste Special for transposition
If you have rows that you’d like to be columns, or columns that you’d like to be rows, it can be a bit frustrating to try and make the desired changes by switching things on a cell-by-cell basis. A more efficient way to make such changes is to copy the data you want to reorient, check Transpose, and paste it with the new orientation.
5. Selecting with Ctrl+Shift
Using your mouse and then dragging the cursor to select data can be time-consuming. A faster way to do this is to click inside the first cell you’d like to select and hold down Ctrl+Shift. Next, do the following:
- Strike either the down or up arrow to acquire the data in the column below
- Strike the up arrows to acquire the data above
- Strike the left or right arrow to acquire everything in the row to the left or right
If you combine the directions, you can acquire a whole column as well as all the data in the rows to the left or to the right. Only cells with data (even if that data is invisible) will be selected. Using Ctrl+Shift+End will cause the cursor to jump to the lowest right hand cell containing data while selecting everything found in between. A method that is faster still is to use Ctrl+Shift+*; use of the asterisk causes the selection of the entire data set regardless of what cell is selected.
6. Utilise Personal Workbook for Macros
When a work is unhidden, you will generally notice a workbook listed that you hadn’t realised had been hidden. This is called the Personal.XLSB file. This file is opened as a hidden workbook each time Excel starts, and is a personal workbook the program creates for you. Macros are the main reason for using it. Creating a macro doesn’t cause it to work in all of the spreadsheets you build by default, instead, a macro is linked to the workbook where it was created. If you use Personal.XLSB to store the macro, this makes it constantly available. To do this, select “Personal Macro Workbook” in the “Store macro in” field when you are recording the macro. To record a macro, turn on the Developers tab, then go to the File tab, choose Options, click Customize Ribbon, then, when you are in the Main Tabs box, check Developers and click OK.
7. How to save your charts as templates
Excel allows you to customise graphs to fit your needs for viewing a particular data set. However, when it comes time to use the same graph again it can be difficult to exactly recreate it. Saving the original chart as a template allows you to avoid this dilemma. To do this, right click on your chart once it has been designed to your satisfaction. Next, select Save as Template. This will enable you to save a file that has a CRTX extension in the default Microsoft Excel Templates folder. Once this is done, to apply the template simply select the data you’d like to chart, navigate to the Insert tab, click Recommended Charts, then select the All Charts tab and the Templates folder. Inside the My Templates box, choose the one to apply and click OK. Certain elements, such as the text appearing in the legends and titles, will not translate if they are not included in the data selected. The template will incorporate all font and color selections, as well as embedded graphics and even series options.
Excel has become the most popular spreadsheet program in the world by offering its users a wide variety of functionality for performing calculations, preparing reports, and analysing a wide array of financial situations. While the tips above by no means cover the full complement of features offered by Excel, they can help you save time and more efficiently operate the software.