Microsoft Excel is one of the most popular spreadsheet programs used today. It is used worldwide by all kinds of people and businesses. There is always something new to learn in Excel. Whether you are starting out on spreadsheets or you want to learn new things, you could always benefit from deeper knowledge of its many formulas. If you run a business and do not have staff conversant with Excel, use freelancer.com to hire well-qualified people who know the program well.
1. Create a table by using shortcuts
One of the most recognised features in Excel is the use of tables that come in continuous columns and rows. They make every activity you carry out much easier, like filtering, sorting, visualising and adding rows and columns to format and maintain the same style.
Many people create tables by clicking ‘insert’ and ‘table’ from the Excel ribbon. There is an easier way of doing it now. Select all the data you need with Ctrl-A, and turn the data into a table by pressing Ctrl-T. Remember to select a name for your table instead of leaving it saved as table 1.
2. Select rows and columns easily
With your cursor looking like an arrow facing downwards and not facing across, click on the column name. A cursor facing downwards will give the whole column reference, while a cursor facing across will give reference to a lone cell. You will not need any readjusting when you add more rows to the table.
A few shortcuts help with this task.
Press shift+spacebar to select an entire row
Press control+spacebar to select an entire column
Selecting a column not in the table but with data in it, place the cursor in the column next to this one. Hit your Ctrl-down arrow, and use the left and right key arrows to transfer or move your data to the column you want. After that press Ctrl-Shift-Up, especially if you have long data.
3. Use VLOOKUP
If you have large data that is scattered all over, do not worry. VLOOKUP is an excellent tool that herds scattered data across different worksheets, and brings it all to one central worksheet.
If you are working on a product list and each product has an inventory number, use that number as your VLOOKUP point of reference. Create the VLOOKUP formula in the menu, and then enter the cell containing the reference number. If you need the information pulled from different cells, enter the range of all the cells.
4. Use slicers to filter data tables
Filtering large data with the small drop down arrows can be cumbersome, especially when you have a huge amount of data to filter. Slicers are of help in such cases. Originally developed for pivot tables, slicers work well with regular tables too.
Place your cursor on the design ribbon and select ‘insert slicer’, and then select all the columns you need to filter.
5. Create an AGGREGATE function
When you have summarised your data within the table and you need to have the updated sum displayed, the usual basic formula might not work. What you need in this case is the creation of an AGGREGATE function within the cell you need to display the sum total.
An AGGREGATE function requires an option number, a function number and a range of cells. It only summarises visible rows.
6. Use pivot charts
There is an easier way to work on complex sets of data. This is by using pivot charts. They have the same functions as the traditional charts with a few exceptions. With pivot charts, you can add filters that are easy to browse through sub sets of data.
7. Unpivot your data
Sometimes you may have fold data, and you need to reshape it from wide to long. Take data from some columns and move the same into rows. Go to the data ribbon, access the query editor, then select all the columns you need to unpivot. Click the tab ‘transform’ and choose the columns you need to unpivot.
8. Create a number of pivot tables
Generate several copies of your pivot table by adding a filter for the column containing categories. Go to the pivot table on the data ribbon, click the analyse tab and then choose options > show report filter pages (Analyse > Options > Show Report Filter Pages) and select the filter you need by clicking manually through every category.
9. Use INDEX/MATCH function to look up data
Another easier way to look up data in an Excel table is by using INDEX and MATCH function keys. There is a numerical location specifically selected by INDEX, given a range of cells within one column or row, and directed on the specific number of cells needed. INDEX works hand in hand with MATCH for it to work well. Based on the results of the search team, MATCH looks for a specific value while INDEX will need to have a location for it to do the search.
10. Use of flash fill
Getting some much-needed pieces of information from a cell can be cumbersome at times. Flash fill helps to solve this problem by automatically adding already formatted data, without the necessity of using any formulas. All you need to do is type the first name you need immediately after the field you are working on, then press Home > Fill > Flash Fill, and you will extract the name from all the fields in the table.
11. Transpose columns into rows (and vice versa)
There are times you work on data formatted in rows, but you need it to be in columns or vice versa. What you do is copy either the row or column you want to transpose. Right click on the destination cell you want, and press on ‘paste special’. You will get a pop window that reads transpose. Press okay to complete all transposition of data.
12. Use Quick Analysis tool
If you need to minimise the time taken to create your charts based on sets of data, the best tool to use is the quick analysis tool. Simply select your data and an icon will appear in the bottom right corner. Click on this, and you get the quick analysis menu. This menu provides you with the following tools: charts, formatting, tables, totals and spark lines. Click on the one you need to get a preview.
13. Add summary rows to tables
For every column, create a total row. This does not generate automatic sums for the rest of the data on the table. You need one summary row at the end of your table, called the total row, and manually select any summary statistic you require. This could be a total sum, total count, total average and many more.
14. Learn VBA
The ultimate step to becoming an Excel power user is learning VBA. VBA is a codinglanguage integrated into Excel that allows you to automate repetitive orcomplex processes. Because VBA is integrated within Excel, it's much moreaccessible than other coding languages. In fact to get started with VBA,you can Record a Macro (Developer > Record Macro) and review the VBA codethat's generated. Try this with a simple process like copying and pasting arange of cells.
There are many other tools and ideas to learn about the Excel program that would perfect your Excel power uses. For people well versed with the Excel tool, the job market is open for them.
If you have anything that might be helpful, do not hesitate to comment and to tell a friend. Leave your remarks in the comments box below.