But if you really want to master it, you can do years of endless work, or join us for a quick look at some of the top 10 methods, tips, and hacks that will help you become an Excel expert in no time.
1. (Short) Switch to Chase
Like most other programs, Excel comes pre-programmed with keyboard shortcuts, so you don't have to click across the screen or navigate through multiple menus to get the job done. The only problem is that there are too many shortcuts to list them all here. However, later versions of Excel will display all the keys you need to press to access options on the Ribbon.
If you press the Alt key to reveal the required key on a tax on the ribbon (such as Insert menu-N), you will see a key for each option. Alternatively, hover your mouse over the desired option to display a tooltip with the corresponding keyboard shortcut.
For example, the keyboard shortcuts for 3D shapes are AT, N, S, and finally 3. Press each key sequentially, not all at once. If you're looking for keyboard shortcuts for specific tasks, Microsoft has a good list for all versions of Excel.
The idea is that you can develop a bit of muscle memory for certain repetitive tasks and act like copy and paste. You can find a few more of them in this article as they are very useful for productivity.
2. Double click to fill quickly
If you have a large data set that needs to run the same formula, there is no need to drag or copy the original formula cell to all other cells.
When you place the mouse cursor in the lower right corner of a cell, the cursor shape and color change.
If you double-click at this time, the formula as the number of consecutive data is copied to the cells.
You can do the same with keyboard (faster): use CTRL D to fill a selection of one or more cells.
3. Making charts and graphs the professional way
Presenting data in the form of charts or graphs is another staple task of most Excel users. There's a wealth of types and formatting options to choose from, though, so it's not always easy to know what's best to use or how to go about doing something.
Here are a few tips to keep your data visualizations looking clean and crisp.
First of all, make sure you match the right chart type to the data you have--for example, data from categorical variables (e.g. revenue per quarter, number of sales of a given item) should be displayed using a bar or pie chart, or a line graph.
Data from sets of continuous variables (e.g. reaction rates vs. concentration) should be displayed using XY scatter graphs. Graphs embedded in worksheets can be aligned with cell boundaries either by holding down ALT when you move it about or by enabling Snap to Grid in the graph's format options (click on the graph, then press ALT, J, A, A, A, P).
Also, don't feel that you need to add a legend to every chart you make in pie charts, there's often plenty of room to add that information into the slices themselves, but rarely so in the case of bar charts. If you want to display two different chart types in the same one, you can do this by highlighting the data you want to show on the same chart, then selecting Combo from the chart selection window (ALT, N, K).
Note that when making a combo graph, it's a good idea to put one of the data series on a secondary axis--this means you can have two different vertical scales showing, boosting the value of the chart. 4. Mastery of One Key
When copying formulas between cells, you may want to always use one fixed value for all other values. To lock a cell, row, or column in a formula, you must put a dollar sign before the letter or number.
Let's say your formula is = C3*D3 and you want to stick to the value in cell C3 and always use it.
The formula now reads = $C$3*D3 and always uses C3 regardless of where you copy the formula. A quick way to apply dollar signs when entering formulas is to press F4 on your keyboard and press Bingo! Selecting a single character, such as a column label, then pressing F4 works just as well.
In Excel, you can also repeat the last action by using the F4 function key. For example, if you have filled a cell with a certain color and want to repeat the same action in other cells, just press F4.
5. Type once, type a lot
If you need to enter the same data into multiple cells, wherever you are, there is a quick way to do it. Select the row, column or cell to which you want to add the same value. If cells are scattered all over the spreadsheet, hold down the CTRL key and left-click on the cells you want.
With all selected cells highlighted, type the value you want to enter (text and/or number), then press CTRL Enter. Now you will have lots of new cells that will be filled beautifully!
6. Quick table, quick chart, quick analysis
Tables are a great way to present data for easy reading, and yes. There is a quick way to stamp data. Select the desired cell on the table and press ALT, N, then T.
A window will appear confirming your selection . Press Enter. Auto-filters for each column, and you can always drag additional rows or columns by-pressing the tag in the lower-right corner.
7. Challenge the big table without fear.
Ordinary Excel users may be faced with the constant need to work with large data tables. Hacking to compare different aspects can be tedious and can lead to errors when trying to read the information.
PivotTables make it easy to analyze and summarize information in large data sets by allowing you to filter data by only the sections you need. PivotTables do not affect the data source, so you can split and modify PivotTables without fear of deleting important items.
Let's say you have something like the table below and you want to compare only some of it. The first step is to press ALT, N, V and finally T. 8. Name the table for easy navigation.
You can use the search function (CTRL F) to find values in a large table, but sometimes it's good to organize all the values by giving them unique names in different sections of the table.
To do this, select the part of the table you want to name, then go to the "Formulas" section of the ribbon (ALT, M) or right-click on the highlighted section. A "Define Name" option will appear. Click it and a new window will open.
Posts
0 Comments