16 Great Excel Skills You Should Learn

Having Excel skill is imperative in this digital world of today. Many organizations and companies all over the world use Excel to carry out their day-to-day tasks such as financial analysis, employee data analysis, recruitments etc.

So being versatile with the Excel spreadsheet comes with so much benefits that can never be over emphasized.

In this article, I will be walking you through some great Excel skills you ought to know about and learn.

Before I dive into our topic of discussion, I would want to encourage and urge you to pay absolute attention to each guide and the short video clip records provided for you. As all that will be required of you in walking this path with me is to concentrate fully and also to practice whatsoever you grasp.

Let's now go straight into business;

Listed below are 16 Great Excel Skills You Should Learn. They are:

1. Data Types

Data Types is a relatively new feature that was added to Excel in 2020.

It allows you to enter text, select data type, then extracts information based on the criteria you select.

Here's a pretty cool example using states where we can pull population data instantly:

2. Custom Ribbon Tabs

The top ribbon in Excel is the home for all of the most commonly used commands.

But what if something you use regularly is missing?

Or what if you want to have a custom group containing only YOUR favorite tools?

Create a Custom Tab, like this:

3. Importrange

The Importrange allows you to import and link a specific range of cells from one spreadsheet to another. i.e This function helps you import a range of cells from a specified spreadsheet.

It can also be called WorkBook links.

The steps:

- Copy & paste the data's link

- Type the tab's name where the data is

- Select the rows and columns from the data

Syntax

IMPORTRANGE(spreadsheet_url, range_string)

4. Detectlanguage & Googletranslate

The first function can identify the language used in a text within a range.

The second can translate a text from one language into another.

DETECTLANGUAGE & GOOGLETRANSLATE are built-in function in Google Sheets, which will allow you to translate from one language to another, and even automatically recognize the language and translate it to your preferred language.

Syntax

- DETECTLANGUAGE(text_or_range)

- GOOGLETRANSLATE(text, [source_language, target_language])

5. a. Query

Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, reshape and transform your data just as required.

This is a powerful function that allows you to do multiple things.

In the example, we filtered the data with the Query function first by sales and then by products.

With this function, you can specify the order of how you want to reorganize the data.

5.b. Query

This function brings a powerful database-style searching to your spreadsheet, so you can look up and filter your data in any format you like.

Here, we selected the products and sales that are in the Paris location.

6. Array Formula

Array formula allows you to perform multiple calculations at once, or, it can perform one or more calculations multiple times within a selected cell range. 

It is a function that can perform multiple calculations on one or more items in an array.

You can see or think of an array as a row or column of values.

In this example, we calculated the net income and profit margin for the first row and it populated the formula in all rows.

Syntax

ARRAY FORMULA(Array_formula)

7. Sortn

The SORTN function lets you sort a range of data and return the first n items from that range after performing a sort.

The SORT function sorts the contents of a range or array in ascending or descending order.

In our example, We first returned the first five products in descending order

Then, we did the same in ascending order.

Syntax

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

8. Transpose

If you have a worksheet with data in columns that you need to rotate to rearrange it in rows, use the Transpose feature.

This function transposes the rows and columns of an array or range of cells.

Syntax

TRANSPOSE(array_or_range)

9. Filter

The FILTER function in Excel is used to filter a range of data based on the criteria in which you specify or choose.

It filters a range based off provided conditions.

In our example;

The first filter was to find sales that were higher than $50,000.

The second filter was to return products of the month of July and in the London location.

Syntax

FILTER(range, condition1, [condition2, ...])

10. Stacking Tables

You can select a range of data from different tables, rows and columns and combine them in one.

That's what the Stack Table function does.

11. Split

This can be useful if you have a text string and want to split it.

You can use this function to split text into different columns with functions.

Here are the steps:

Select the text or column, then click the Data menu and select Split text to columns.

Select the delimiter your text uses, and your text will be split.

12. TextJoin

The TEXTJOIN function combines the text from multiple ranges or strings, and includes a delimiter you specify between each text value that will be combined.

If you ever wanted to join multiple strings of text in one cell. These are the two methods to use.

The first one is A2&" "&B2.

The second one is to use the concatenate function.

Syntax

CONCATENATE(string1, [string2, ...])

13. Googlefinance

The GOOGLEFINANCE function allows you to import real-time financial and currency market data straight into Google Sheets.

The GOOGLEFINANCE allows you to track current stocks and shares information.

It searches the current or historical securities information from Google Finance.

In our example;

We selected from the date of 1/1/2022 to today. 

Syntax

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

14. Unique & Sort

The UNIQUE and SORT functions provide a more eloquent way to list unique values and count unique values.

Returns unique rows in the provided source range, discarding duplicates.

In our example;

We first retrieved a unique list from our data.

Afterwards, we sorted the list by alphabetical order.

15. Custom Lists

Stop wasting time entering the same customer names, product types, and generally repetitive data.

Creating custom lists is easy.

• Excel

• Preferences

• Custom Lists

• Create and click "Add"

Type the first item, then drag, and the rest of your list appears!

16. Combine Text using "&"

Let's say you have a list of First & Last names you need to combine.

• Select the destination cell

• For our example the function will be: 

  =A3&" "&B3

• Press "Enter"

It's that easy!

For staying with me to the end, here's a bonus tip for you!

Bonus

Did you know you can paste data from Excel into Word or PowerPoint and it will update automatically?

Just;

• Copy from Excel

• "Paste Special" in Word

• Select  "Paste link"

• MS Excel Worksheet Object

The picture in Word updates as data changes in Excel!

And we are done for today!

If you find this piece to be very helpful, I'll kind want you to share with your friends and colleagues.

Also, the comments section are open for reviews and contributions.

Happy Learning!