With Excel, creating charts and tables, analysing data, using autofill, formatting, and applying filters has never been easier. Whether you need to summarise, highlight, calculate, or transform data, Excel offers powerful tools to save you time with just a few clicks.
With so many different functions, almost anyone can find a use for Excel. Although its vast capabilities may seem overwhelming at first, mastering Excel provides students with a significant advantage in their studies and future careers.
1 – Recommended ChartsWith the newly added recommended charts feature, you can quickly create a chart that best suits your data. This saves time, so you don’t have to test every chart manually. After entering your data with column headers, go to Insert > Recommended Charts. A pop-up window will display the best options for your dataset. If none appeal to you, click the All Charts tab to explore other chart types.
2 – Pivot TablesPivot tables are one of the most powerful and underutilised Excel tools. Particularly useful for large datasets, pivot tables allow you to summarise, analyse, and explore your data efficiently. Go to Insert > Pivot Table and select your data range. A window will open on the right side, where you can choose fields in the top half and build your table in the lower half.
Tip: Like recommended charts, Excel also offers Recommended Pivot Tables, suggesting layouts based on your data.
3 – TransposeWhen you need to switch rows and columns, the transpose feature lets you do so without copying and pasting each header manually. To transpose data, right-click and select Copy. Then, choose where to place the transposed data, right-click, and select Paste Special. In the pop-up window, check the Transpose box and click OK. Your rows will now become columns, and vice versa.
4 – VLOOKUPIf you are working across multiple sheets or workbooks, VLOOKUP allows you to merge data to create reports and summaries. This function is ideal for combining datasets that share at least one identical column in both sources.
5 – Quick AnalysisQuick Analysis is one of Excel’s latest features, making data analysis faster than ever. You can instantly create different chart types, apply table styles, generate pivot tables, insert quick totals, and apply conditional formatting.
Some key Quick Analysis tools include:
- Formatting: Highlights key data points with bars and colours to help you identify trends.
- Charts: Recommends the most suitable charts based on your selected data.
- Totals: Allows quick calculations for columns and rows, including running totals.
- Tables: Makes sorting and filtering data easier.
- Mini Charts: Small charts displayed alongside your data for quick insights.
6 – AutofillAutofill is a time-saving feature that detects patterns in your data and automatically completes the rest. For example, if you want to list even numbers up to 100, start by entering 2, 4, 6, and 8 in consecutive cells. Then, hover over the bottom-right corner of the selection (until a black cross appears) and drag down to fill in the remaining numbers.
7 – Conditional FormattingConditional formatting makes it easy to spot trends and outliers in your data. It allows you to change cell colours based on their values. For example, you can highlight numbers below a certain threshold in red.
To apply conditional formatting, select the cells, go to Home > Conditional Formatting, and choose an option from the dropdown menu. A pop-up window will prompt you to specify your rule.
8 – Dollar Signs ($) in FormulasUsing dollar signs in a formula ensures that a specific column and row remain the same, even if you copy and paste the formula into other cells. This converts a relative reference into an absolute reference.
For example, instead of writing = A5 + C5
, you can lock the cell references by writing = $A$5 + $C$5
.
9 – FiltersFilters help you analyse specific data within a large dataset by displaying only the entries that meet your criteria (without modifying the original data). To apply a filter, select your dataset and go to Home > Sort & Filter > Filter. Small dropdown arrows will appear next to each column header, allowing you to filter the content as needed.
Universia, 03/10/2018