Microsoft Excel is a popular business software package. It’s incredibly powerful and features are regularly upgraded to make it better.
Excel is a powerful tool that can improve your productivity, efficiency, and speed. This article will walk you through 12 tips and tricks that you can use to harness the power and impress your boss or colleagues.
How to master Excel quickly in 12 steps
1. Learn to navigate the interface
Where do you start? Our recommendation is to learn how to use the Excel interface.
Let’s begin with the basics
Use the Tab key to move the cell to the right when typing in Excel.
Use Enter to move the cell down one row. Enter is the same as tab if you’ve been moving through the columns using the tab key. It will move you down one row and then back to the first cell of the column.
The Ctrl and Up, Down, Right, or Left directional arrows will bring you to the cell that was last used in the direction. It is a great way to navigate through large Excel lists.
By pressing the Ctrl and Home keys together, you will be taken to the first data cell of your range.
2. Learn some useful shortcuts
You can speed up your Excel work by learning some keyboard shortcuts and also using your mouse or touchpad.
Start by learning keyboard shortcuts. You can learn the copy-and-paste shortcuts Ctrl+C for copying and Ctrl+V for pasting. Plus, Ctrl+Z can be used to undo any previous actions.
When you start using these Excel shortcuts, keep learning.
To enter the date of today in a cell, I use Ctrl+ ;. Double-clicking on the fill handle in the lower right corner of a cell will copy the data to the end of a list.
Excel offers over 500 keyboard shortcuts, and many other useful tools to speed up your work.
It’s a good idea to learn some of these Excel shortcuts, even if you don’t use them all.
3. Freeze panes
Freezing Panes allows you to keep your labels and headings visible while scrolling around the spreadsheet. This is a must-know skill.
In most cases, the headings of a spreadsheet will only be in the first row. To freeze this area click View > Freeze panes > Frozen Top Row.
You can freeze any number of rows and columns you want.
Click the first cell following the rows or columns that you wish to freeze. Click on the cell B4 if, for example, you want to freeze three rows and one column.
Click on View > Freeze Panes and then select the first option.
When you scroll down the first three rows are still visible. If you scroll right, the column A will remain visible.
4. Understand Excel formulas
Excel mastery is a matter of being proficient at formula writing. Excel’s muscles are these.
You will be a step above everyone else if you are able to perform basic calculations or use more complex formulas.
Start by creating simple calculations to add, subtract multiply and divide values. Learn some of the most commonly used functions. Included are SUM, VLOOKUP and COUNTIF.
You can do anything once you become comfortable with formulas. Excel’s charts, Conditional formatting rules and other features can be made more powerful by using formulas.
Excel Formulas and Functions .
5. Learn how to create a simple drop-down list
Excel dropdown lists can make it easier to enter text and ensure accuracy.
Create a dropdown list in Excel
1. Choose the cells in which you wish to see the list.
2. Click Data > Validation of Data.
3. Select list from the Allow list.
4. You can either type in the list items, separating them with a comma, into the source text box. Select a range that contains the list items.
You can now enter data easily and reliably with a dropdown list.
6. Visualize key data with conditional formatting
Conditional formatting is a popular feature of Excel. It allows the user to quickly understand the data.
Create simple conditions that automatically format cells when a goal is met, a deadline passed, or perhaps sales dropped below a threshold.
If a cell value is greater than 300, we would like to change it to green.
1. Choose the range of cells you wish to conditionally format.
2. Click Home > Conditional formatting > Highlight cells rules > Greater Than.
3. Enter 300 and choose the format you wish to use.
There are many formatting options.
Excel also allows you to use data bars and iconsets with conditional format. These visuals are very powerful. Below is an image showing data bars for the same range of values.
7. Get things done faster with Flash Fill
Flash Fill allows you to manipulate data quickly. This feature will greatly reduce the amount of time it takes to perform data cleansing tasks that were previously done using Excel macros and formulas.
Consider the following list. We need to extract the names from the column A list.
Flash Fill will ask you if you want to repeat the pattern. This is incredible!
Once you press Enter, the data will be extracted.
Excel Flash Fill can be run in several different ways. The button can be found on the Datatab in the Ribbon. You can use the shortcut Ctrl +E to operate it.
8. Learn visual storytelling with charts and graphs
If you want to master Excel, you will need to learn how to visualize numerical data. Some people will not want to compare and examine endless rows of data in order to find patterns and trends. You can highlight the most critical information by using a graph or chart.
How to create a chart in Excel with 3 simple steps
1. Source data should be highlighted.
2. Select Insert on the Excel Ribbon to select the graph type you want.
3. Format the chart as you wish using the customization options available.
Chart Customization
Excel offers a virtually unlimited number of ways to customize charts. The chart design tab appears only when the chart is selected. You can use this menu to change the style of your chart or its location. It also allows you to add other elements such as a legend, data label, and axis title.
If you feel that another chart would better tell the story, you can change it.
9. PivotTables: Summarize Data
Pivot Tables are among the most powerful tools in Excel. Summarizing large datasets is as simple as 1, 2, 3.
Excel PivotTables can be used to sort a large list of sales into regions or to look at sales of specific products.
These pivot tables are not only powerful but also easy to use. Excel’s PivotTable tool simplifies the creation of reports and does not require complex formulas.
To create a PivotTable:
1. Click the data list you wish to summarize.
2. Click Insert > PivotTable.
3. In the Create PivotTable dialogue, make sure the range is correct. Also specify whether the PivotTable should be a new worksheet or an existing one.
4. Create your Excel PivotTable by dragging and dropping fields from the list of fields into the four areas.
The image below shows the year in Columns, product categories in Rows, and the value of the sales in Values.
Excel’s Values section is where you perform calculations like sum, count, and average.
You can change the calculation by selecting Summarize Values By from the right-click menu, and then selecting which Excel function you wish to use.
Excel PivotTables can do much more, but this is not the focus of this article.
PivotTables are a great way to explore all the possibilities. Grab some data from a spreadsheet, insert a PivotTable, and begin dragging fields into the different areas. Excel is a tool you can only learn by using Davinci School.
10. Learn how to protect worksheet data
Excel features like PivotTables and formulas will not work without accurate data.
Using Flash Fill and creating drop-down list are just a few of the ways this article shows you how to keep your data accurate. Protecting data is an important step.
Excel offers a variety of protection options, but protecting the sheet is the most common.
The process of protecting a sheet involves two steps. You must first specify which cells you don’t want locked. So, users can continue to edit the cells. You then apply the protection.
- You can select the ranges of cells you wish to unlock. (Hold down the Ctrl button to select multiple ranges simultaneously).
- Open the Format Cells dialog by pressing Ctrl+1.
- Click on the Protect tab. Uncheck the Locked checkbox and click OK.
- Click Review> Protection Sheet.
- It is possible to enter a passcode for additional security. However, it is not required. Click OK to confirm the functionality that users will be required to perform.
Unlocked cells are still editable, but only with limited functionality. When you attempt to edit a cell that is locked, you will receive a warning message.
11. Power up with Power Query and Power Pivot
Knowing these two Power Tools will help you go beyond the average user.
Power Query allows you to import data and prepare it for analysis. You can find it on the Data tab of the Ribbon.
Power Query allows you to import data from a wide range of sources (which are constantly changing), including CSV files, the Web, and folders.
The Power Query Editor provides a user friendly environment for a variety of cleaning and shaping functions such as splitting columns and formatting data, removing duplicates, and unpivoting it.
Power Query is based on a programming language called M. It’s difficult to learn and fortunately, not necessary for 99% Excel users. The editor has everything a user could need.
Each edit is saved as a “step” and can be refreshed at any time by clicking a button.
Power Pivot, also known as the Data Model, is a tool which allows the storage of large volumes of data. Power Pivot allows us to store data beyond Excel’s physical limits.
Power Pivot allows you to create relationships between tables and also write functions called DAX functions.
The power of DAX and the ability to create models is far greater than what you could get from a spreadsheet.
Anyone who is involved in the analysis of large quantities of data, or performs complex analyses should be familiar with this tool.
Bonus: Check out Ken Puls’ lesson from his Power Pivot course which explains what Power Pivot is, and how it can streamline your workflow.
12. Create Macros with VBA
Excel macros allow you to automate repetitive actions to boost your productivity.
The tasks may be complex but are usually simple and repeated by most users. Macros allow you to accomplish tasks faster and more reliably.
Start using macros by recording your Excel actions. This will produce the VBA code, and a macro.
