How to Use Excel

Key takeaways

• Understanding how to use Microsoft Excel includes learning about key functions and formulas, such as VLOOKUP and PivotTable, working within cells, and using keyboard shortcuts.

• You can use an Excel PivotTable to quickly extract meaningful insights from large quantities of raw data, such as sales trends or customer demographics.

• You may choose to use VLOOKUP in Excel to retrieve data from a single column, allowing you to collect and analyze data quickly.

You can use Microsoft Excel in various career fields, including accounting, data analysis, and project management. Understand Excel’s key features, practical tips, and how to maximize this versatile application in your personal and professional life.

If you’re ready to build your Excel skills, consider enrolling in Macquarie University’s Excel Skills for Data Analytics and Visualization Specialization. In as little as four weeks, you’ll have the opportunity to learn how to use advanced Excel functions, build creative visualizations, and understand powerful automation features to streamline your work.

Key functions and formulas in Excel

Understanding the program’s primary functions is the key to unlocking its benefits, which include helping you save time and reduce errors. Explore some of the main features to better grasp all Excel has to offer.

VLOOKUP

VLOOKUP (short for “Vertical Lookup”) is a popular and useful Excel function that retrieves data from a particular column. It’s especially invaluable when working with large data sets because it helps you find and examine information quickly. It can save time and mitigate the risk of mistakes that often accompany manual data retrieval, making it an essential tool for professionals working with complex data sets.

The formula works like this:

1. Enter the lookup value: Select the cell containing the value you want to search for.

2. Highlight the table array: Identify the data set you’ll be searching and select the range.

3. Specify the column index: Pinpoint the column containing the data you want.

4. Set the range lookup: Choose TRUE if you want the program to locate approximate matches or FALSE if you want exact matches.

5. Apply the formula: Put the formula into action. Altogether, that’s =VLOOKUP(lookup value, range, column number, and match type).

PivotTables in Excel

PivotTables are dynamic Excel tools that allow you to perform powerful calculations and work with large quantities of data, analyzing, summarizing, and presenting it in an easy-to-understand format. With PivotTables, you can quickly extract meaningful insights from raw data (such as sales trends or customer demographics). They’re beneficial when creating summary reports, generating insights, or presenting data in an easily digestible format.

The steps to create a PivotTable are as follows:

1. Prepare your data: First, you must make sure the data is free of errors, accurate, and free from blank rows or columns.

2. Insert a PivotTable: Go to the Insert tab and click PivotTable. Then, you can select your data range.

3. Drag fields: Assign fields to rows, columns, or values to organize your data.

4. Customize the view: Use filters, sort options, and formatting to create a more visually appealing table.

How to use Excel: Working with cells

Cells are the areas where rows intersect with columns. Knowing how to merge and perform other functions can help improve the quality of your work and enhance your ability to organize data.

How to merge cells in Excel

You can combine multiple cells into one, a function best used for creating headers or titles across more than one column in your spreadsheet. However, overuse can complicate sorting and formula application within the Excel sheet, so use them sparingly.

To perform this function, you must:

1. Select cells: Highlight the cells you want to merge.

2. Merge options: Go to the Home tab, select Merge, and choose your preferred merge type.

3. Adjust alignment: Ensure that the content aligns correctly inside the merged cell using the Align feature.

CONCATENATE in Excel

This function combines text from multiple cells and puts it into a single cell. This feature is useful for creating custom strings, such as linking first and last names. Because manual re-entry isn’t necessary, this method of combining data can save you a lot of time and effort.

To use CONCATENATE, you must:

1. Select the cells to combine: Identify the cells with the text you want to merge.

2. Apply the formula: Use =CONCATENATE( or =CONCAT( to join text with a space or another separator.

3. Refine the output: Use additional characters such as commas or dashes to format the results further.

Read more: How to Convert Excel Columns to Rows: A Practical Guide to Transposing Data

Additional tips for using Excel

From Excel’s key features to its helpful formatting tools and time-saving shortcuts, this spreadsheet software program can help you perform many tasks. Check out a few tips to help you get the most out of it.

Keyboard shortcuts

Using these shortcuts can significantly boost efficiency, helping to optimize your productivity when using Excel. They reduce the amount of time you spend searching through menus for tools, allowing you to focus on the task at hand. These include:

• Copy and paste: Ctrl + C, then Ctrl + V

• Undo an action: Ctrl + Z

• Create a table from a selected range: Ctrl + T

• Insert the date: Ctrl +;

• Open the Home tab: Alt + H

Formatting data

Proper formatting helps make sure that your spreadsheets are easy to read and simple to interpret. By being conscious of how your Excel sheet is formatted, you’ll improve readability and ensure the utmost professionalism. Formatting options include:

• Conditional formatting: Highlight data points that meet specific conditions, such as values below a set point, to identify trends and patterns in your data.

• Data validation: Limit data entry to specific types, such as dates or whole numbers.

• Cell styles: To maintain consistency, use predefined styles for headers, totals, or emphasis.

Resources for learning Excel

Mastering Excel requires frequent practice and reliable access to plenty of learning sources to help you along the way. Introductory books such as Excel All-in-One For Dummies can provide a general overview for beginners. At the same time, YouTube and other video platforms can walk you through more advanced features and techniques via visual aids. Structured courses on Coursera can also give you guided learning experiences tailored to your specific skill level. Combine these resources with continued practice with Excel to gain a much stronger understanding of this helpful program.

How can I learn Excel quickly?

To learn Excel quickly, explore Excel certification programs and courses that can introduce you to key Excel topics like modifying and formatting data, creating visualizations, and using keyboard shortcuts. These programs are often self-paced, allowing you to expedite your learning by working through the program efficiently.

Who uses Excel?

From finance to marketing to education, Excel is a valuable tool for professionals across all sorts of industries, including:

• Accountants: To create balance sheets, manage budgets, and perform financial analysis

• Data analysts: For processing large data sets, visualizing trends, and creating predictive models

• Project managers: To track milestones, budgets, and task progress

• Marketers: For analyzing customer data, tracking campaign performance, and managing leads

Find free Microsoft Excel resources

Visit our Career Resource Hub, where you can assess your skills and explore career paths. Then, explore the following free resources to learn more about how to use Excel:

• Bookmark this page: Excel Terms & Vocabulary

• Practice with a tutorial: How to Use Dynamic Named Ranges in Excel

• Learn essential formulas: Excel Formulas Cheat Sheet

Accelerate your career growth with a Coursera Plus subscription. When you enroll in either the monthly or annual option, you’ll get access to over 10,000 courses.