Although the application’s great capacity can be intimidating, there is no doubt that knowing how to use Excel offers any student an advantage in their studies or future workplace.
1- Recommended Graphics
Using the newly added recommended charts, you can quickly create a suitable chart for your data. This feature saves time so you don’t have to test all the charts before choosing the best one.
After inserting your data in Excel with column headings, go to Insert> Recommended Charts. A pop-up window will show you the best options for your data. If you don’t see a chart you like, click the All Charts tab at the top to see all the types of charts available.
2- Pivot Tables
The pivot tables are arguably one of the most powerful tools Excel and underestimated. Especially useful for large data sets, pivot tables are a great way to summarize, analyze and explore your data.
Go to Insert> PivotTable and select your data range. A window opens on the right, where you can select your fields in the upper half of the sidebar and create the table in the lower half.
Suggestion: similar to the recommended graphics, there is also the option Recommend pivot table. With this feature, Excel suggests layouts based on the data provided. If you’re just starting with pivot tables, it’s a great place to start.
3- Transposition
When you want to exchange your rows for columns (or vice versa), the transpose feature allows you to simply do this without manually copying and pasting each individual header.
To transpose a column into a row (or vice versa), start by right-clicking and selecting “Copy”. Then, select the cells where you want your first row or column to start, right click on the cell and select “Paste Special”. A window will appear – check the “Transpose” box in the lower right corner and click OK. Now your row becomes a column or vice versa.
4- VLOOKUP
If you are working on different sheets and workbooks, VLOOKUP will allow you to combine data to create reports and summaries. If you need to combine data sets, VLOOKUP is your formula of choice. When using this formula, make sure you have at least one column that appears identically in both places.
5- Quick Analysis
The quick analysis feature is one of the latest features in Excel. It used to take a bit of work to analyze your data, but Quick Analysis makes it even faster. You can instantly create different types of charts, including line and column charts and mini-charts. You can also apply a table style, creative PivotTables, insert totals quickly and apply conditional formatting. If you’re not sure which one to use, see the details:
Formatting allows you to highlight parts of your data by adding items such as data bars and colors. This allows you to see high and low values quickly, among other things.
- Charts shows you recommended charts based on the data you have selected.
- Totals allows you to calculate numbers in columns and rows. For example, Running Total enters a total that increases as you add items to your data.
- Tables facilitate data filtering and classification.
- Sparklines are tiny charts that you can show alongside your data.
6- Autocomplete
The AutoFill allows a great saving of time that anyone can use. Excel looks at patterns in its data set and automatically completes the rest. For example, if you only want to include even numbers up to 100, you can start by inserting 2, 4, 6 and 8 in the cells, hovering over the bottom right corner of the data selection (you should see a black cross) and dragging down until that has the data you need.
7- Conditional Formatting
The conditional formatting makes it easier to spot trends, outliers, or an overview of your data. Specifically, it allows you to change the color of a cell based on the cell’s information. For example, you can highlight numbers that are below average in your Excel sheet.
To apply conditional formatting to your data, first highlight the cells you want to apply. Select “Conditional formatting” on the Home tab and choose an option from the drop-down menu. A pop-up window will ask you to provide more information about your formatting rule. Click OK when finished and you will see your results immediately.
8- Dollar signs
You can use dollar signs in a formula to ensure that a specific column and row remains the same – even if you copy and paste that formula into other cells. In other words, dollar signs allow you to change your Excel formula from a relative to an absolute. To make this change, just add dollar signs before and after the row and column values, like this: from (= A5 + C5) to (= $ A $ 5 + $ C $ 5).
9- Filters
The filters are a great way to analyze specific data on a large data set. With filters, you can reduce your data to see just what fits certain criteria (without changing the original data set). In Excel, you can add filters to each column and choose which cells you want to view at the same time.
To start filtering, select your dataset, Home> Sort and filter> Filte
