15 Features That Make Excel “Excel”

Hello everybody,

Here we are with another long article again.
As you know, Excel has been an indispensable part of our office life for a long time. We wanted to compile The Features That Make Excel “Excel”.
There are so many topics, so many features… But in this article, we will be talking about the most important features of Excel that you need to know.

Let’s get started.

What are these features?

‣ Shortcuts
‣ Formulas (Functions)
‣ Pivot Table
‣ Conditional Formatting
‣ Data Validation
‣ Sort
‣ Filter
‣ Advanced Filter
‣ Wildcard Characters
‣ Charts
‣ Flash Fill
‣ Find and Replace
‣ Convert Text to Columns
‣ Al Import External Data
‣ VBA (Macro)

► SHORTCUTS

It is disputable that if the shortcuts in Excel are a feature, but it is a very important feature for us. Because shortcuts allows us to access and apply the existing features faster.
Being able to do that is very valuable.

While creating pretty much any program, some keyboard shortcut keys are assigned to some menus and features so that they are accessed in a quicker way. In addition, there are global shortcuts that are frequently used in the computer world.

You can download the 40 Most-Used and Time-Saving Shortcuts in Excel in both Turkish and English below.

Turkish
English

With these shortcuts, we can act faster in Excel and get our job done quicker. And also, they allow us to work more confidently. This way, we sum the table quickly, select the whole table with one action, add filter quickly, and become faster by using the keys assigned to a feature instead of looking for that feature in the menu.

► FUNCTIONS (FORMULAS)

No doubt that one of the biggest reasons why we use Excel is the built-in functions it contains and allows us to use. In a daily use, we usually prepare tables and want to sum the financial numbers, sales stock numbers; produce their statistics and analyze them. Excel offers us many tools for that, and one of them is the Functions.

There are many menus in the Ribbon in Excel, and many features in those menus that are related to the menu’s name and also that are not. But the Formulas menu only has to do with the Formulas. It is a pretty broad subject. Thus, Microsoft has created a menu just for this subject.

There are many formula categories. If we know the functions we need, we start the formula by writing that function’s name directly. But if we don’t know, then we can find the suitable function for our need in the Formulas menu and continue that way.

Many functions in Excel are named wisely based on what they do. For example, if we want to get a year, month or day our of a date, they already have their functions as: YEAR, MONTH, DAY. We can use them. There are many examples of this situation. For example, there are MAX and MIN functions to find out the lowest or highest number in a column. And the MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

The working principle of functions is like this: they require some arguments from you and if you give it those arguments, the formula executes the necessary look up and calculation, and returns us a value. Some of these arguments are required/obligatory, and some are optional. Even if you do not enter them, the formula works. But a different result might be returned when the optional arguments are entered. So, it is important to make a good decision about the options.

There are also functions that can change instantly, daily, or with each calculation. For example: NOW(), TODAY(), RANDBETWEEN(). If you write the =TODAY() formula into a cell, it returns you that day’s date. And when you open that file the next day, it returns the date of the next day. Likewise, when you write the =NOW() formula, it will return the date and hour of that moment.

10 Most Popular Functions Stated by Microsoft

► PIVOT TABLE

Pivot Table is a strong tool that helps you to calculate, sum and analyze the data in order to see the comparisons, patterns, and trends of your data. You can access it through the Tables group in the Insert menu.

We use this feature when we have a big table and want to create pivot tables based on the fields we want. Even though it is very easy to use, it allows us to execute a very strong and fast data analysis and prepare reports. If you have a table like this, you can get many summary reports from it. If you have a date field in your table, you can create reports by years, quarters, months, weeks and days for all the dates.

You can get many reports from the table below like What is the total amounts and costs based on the Company, import no or product reference; or based on year, quarter, month, or week?

After creating a Pivot Table, the Field List will come to the right. You can select the fields you will use in the summary report or drag and leave them to shape your report.

After creating a Pivot Table, you can edit the design of the report via the Analysis and Design Menus and make its visualization better with a chart by filtering quickly in your report with Slicers, Timeline and Pivot Chart that are Pivot Table tools offered to us.

► CONDITIONAL FORMATTING

Another frequently used feature in Excel is the Conditional Formatting. This feature is usually used to highlight the cells that meet a specific condition. It is in the Styles group in the Home menu.

There are some ready-made conditions that you can use. And when those ready-made conditions are not enough for us, we can create our own rules with formulas and format the cells that meet these rules. Of course, for this you need to know about the formulas first.

We can see the ready-made rules offered to us when we choose Conditional Formatting as below. We can easily format by number, text and date.

If these rules are not enough for us, we can choose the “Use a formula to determine which cells to format” from the New Rule option and write the formula and highlight the cells that meet our conditions.

We can create multiple Conditional Formatting in a field if we want. We can use the Manage Rules option to see or change the conditions of these formattings.

We can use the Clear Rules option to clear rules in a certain field of the page or all the rules in the page.

You can see the examples about Conditional Formatting that have been prepared based on the Data Bars, Color Scales, and Icon Sets rules. You can bring light to your tables and get visual analysis by using formatting rules.

And on top of that, you can highlight without having advanced-knowledge like macros with this built-in feature.

You can see a few examples about the Data Bars, Color Scales, and Icon Sets rules below.

► DATA VALIDATION

We use the Data Validation feature a lot in Excel too.
We can define it like this: It is a feature that enables a certain data to be entered into a cell or cell range and blocks any other data to be entered there. It is in the Data Tools group of the Data menu.

We already have ready-made Data Validation criteria. You can see these criteria in the image below.

Whole Number – to restrict the cell to accept only whole numbers.

Decimal – to restrict the cell to accept only decimal numbers.

List – to pick data from the drop-down list.

Date – to restrict the cell to accept only date.

Time – to restrict the cell to accept only time.

Text Length – to restrict the length of the text.

Custom – for custom formula.

We can create custom criteria with formulas in the List and Custom criteria. If we want, we can create dependent data validation lists as well. For this, you need to create the table appropriately and define the data ranges with the Define Name feature. And then you can relate them to each other.

► SORT

After we write titles in our tables and enter the data one under another or in a prepared list, the data is not always sorted Alphabetically, Numerically or by Date and we feel the need to have our data sorted. Here comes the Sort feature of Excel to our help.

We can access this feature through many ways:

• Through the Editing group on the right of the Home menu
• Through the Sort & Filter group in the Data menu
• Through the Quick Access Toolbar if we add it
• By clicking the Filter arrows activated for filtering
• By coming to Sort in the Right Click menu

The most important mistake made while using the Sort feature is to leave a black column in our table. Sometime we add a column and then hide it instead of deleting and when we sort the table, our table is separated into two and the left and right sides are seen as separate tables. Thus, our table loses its general integrity in any sorting and the data get all mixed up. Such a situation can cause fatal consequences.

If you will sort just one column, it will be enough to choose any cell in that column. You don’t ever have to choose the whole table or a whole column.

And when you want to sort multiple columns, you should use the Custom Sort window.

With the fields in this window, you can sort on Cell Values, Cell Color, Font Color or Conditional Formatting Icon with alphabetical, numeric, date, cell color and Custom Lists order.

► FILTER

We can filter in a field or multiple fields of our table and make only the data we want visible. For this, we use the Filter feature frequently. It is one of the main features of Excel and is usually located next to the Sort feature. So, we can access the Filter feature through the ways we can access the Sort feature.

Shortcut: Ctrl + Shift + L  – ( Add Filter if there is none in the table, removes it if there is. )

There is Automatic Filter and Advanced Filter in Excel. We can use the wildcard characters in both of them. In the Filter feature, you can filter more quickly and in a custom way by using the

• Text Filters
• Number Filters
• Date Filters

depending on the data type you want to filter.

Also, if the column was highlighted, you can filter the data with the Filter by Color option. You will see that the cells with the color you choose are filtered.

We have mentioned that you can use the Wildcard Characters when looking up a data.
You can especially use the * character in the Search box and limit your search.

► ADVANCED FILTER

As you know, we can filter by maximum two conditions as and/or in a field with the automatic filter feature. We can use the Advanced Filter feature to filter the data in our table by multiple conditions or to list the results in another field. This feature is right under the Filter with the name of Advanced in the Data menu.

This feature offers us two things:

• To list unique expressions in the stated field or fields on another field
• To filter the data that meet columns and criteria stated in another field on the table or list the results in another place.

You can find the example of the first article below.
Here we say this: List the Unique Records in the F1:F193 cell range starting from the N1 cell. You can see the result in the N column.

You can find the example of the second article below.
Here we say this: If the A1:K193 cell range has the data in the M1:O3 range, list the results starting from the M6 cell.

► WILDCARD CHARACTERS

Just like in many programs, we can use the Wildcard Characters given to us in formulas or some features of Excel. These Wildcard Characters help us to find expressions that are in the beginning or end of a text or within it by writing a certain part of the text.

There are three different Wildcard characters.

•  * (Asterisk)
•  ? (Question Mark)
•  ~ (Tilde)

They are used in looking up letters, and words.
We can use the Wildcard Characters as comparison criteria for the text filters and while searching and changing the content.

* (asterisk) It is used for multiple characters. Covers all the characters in the keyboard.

For example: “İst*” Means the ones that start with İst.
For example: “*ist” Means the ones that end with İst..
For example: “*ist*” Means the ones that has İst in it..

? (Question Mark) It is used for any character.

For example: “Mura?” means that it starts with Mura and is followed by a letter.
For example: “?urat” means that it starts with a letter and is followed by urat.
Helps you to find expressions like Murat, Surat

~ (tilde) is used to find the ones that has a question mark, asterisk or tilde.

You can press ALTGR + Ü and then space to write this character. (In a Turkish keyboard) To create the tilde symbol using a U.S. keyboard hold down the Shift key and press tilde key.

It is used to search/state the Asterisk, Question Mark and Tilde.
For the Asterisk and Question mark, it means that you don’t represent one or more characters, you are just a star or question mark.

We have mentioned that they can be used in formula. Let’s have an example of this if you wish.
It will be enough for us to write “SU*” (“WATER*”) into the criteria part in order to find out the Total Product Sales Amount of the ones that start with SU in the Product (Ürün) column.

We have also mentioned that you can use the Wildcard characters in the Filter feature.
When we write *e? into the Search box of filter, it means that it stars with any characters but the penultimate character is e and ends with any text.

► CHARTS

Charts help you to make your data more visually-pleasing to be more impressive on your target audience. You can prefer charts that are more appealing that presenting with just plain tables. Excel offers us many chart types at this point. It has become easier to create chart especially with Excel 2013 and forward. Now, we don’t think about which chart type we should pick thanks to the suitable chart recommendations for our tables.

We need to simplify our data as much as possible for our chart to be easily comprehended. You can add chart title or axis title to any chart type. The axis type normally can be used in all axes that can be seen in the chart and depth (series) axes in 3-D charts are included. Some chart types (like radar charts) have axes but they can not view axis titles. You can not add axis titles to charts that don’t have an axis (like doughnut or pie charts).

► FLASH FILL

This amazing feature has entered our lives with the Excel 2013 version.
Now, we can see the results that we could only get by writing a few nested formulas before with one click.
You can find this feature in the Data Tools group of the Data menu.

We can say that we are able to get things done that we could only get done with Text Functions or Convert Text to Columns Wizard with one feature.

We have to have this logic while using this feature: first, we state the part we want to get from the main text and how we want to get it in a cell, and then apply it to the other cells with the CTRL + E shortcut.

What Can We Do with This Feature?

• Separate Names and Surnames in a single cell to separate columns.
• Join data in separate columns in a single cell.
• Separate the data you want to get from the data with spaces that is in the beginning, end, or middle of a text.
• Sort out the text and numbers in the cell.
• Convert a text or number to the format you want.
• Change all the letters of the text to capitals.
• Change all the letters of the text to lower case.
• Get the initials of each word.
• Create a date from the day, month and year expressions that are in separate cells.
• Turn the decimal points to comma.
• Separate the decimal part and whole number from a decimal number.
• Convert the phone number in the cell to any format you want.
• Separate the username and domain parts of e-mail and use it in many other text editing actions.

► FIND and REPLACE

As you know, in Excel we can call the Find and Replace window with the CTRL + F and CTRL + H shortcuts while looking for a data in an active cell or in the workbook. We use this feature frequently. And the first thing we should do while using it is to click the Options button. Because, the last search we have made stays in the memory and the choices come as marked if a choice was made out of options before when we call the window and these affect the next search.

Any text written into the Find what box is looked up in the worksheet as contains and if it finds, lists the cells under the window.

Here is a tip: as you can see in the table above, when we make a search, all these are listed and the first record is marked under the window. Since it is marked, we can select all the records with the CTRL+A keys. And you can highlight, delete etc. all the selected cells at once after selecting all.

In addition, we can limit the search with the “Match case” or “Match entire cell contents” options. If you want, you can use the wildcard characters ( * ? ~ ) to search faster and more specifically.

And we can replace the that we looked up with other data in the Replace tab. We can limit the search again by choosing among the options. If you are looking for a format, you can replace it with another format.

We can get extra special work done by using the Wildcard Characters in the Replace window. For example: there are both text and numbers in cells one under another in a column and there is just a space between these two data, just like you can see below. We can say “leave all the characters before the space blank” by using * in the Replace window. This way, all the characters before the space will be erased and only the numbers will be left. For this, write star space (*) into the Find what box and leave the Replace with box empty.

► CONVERT TEXT TO COLUMNS

Sometimes we want to get data from Text or CSV files in Excel, or we receive a ready-made table. In these files the data is usually showed next to each other with commas or semi colons. We can separate these data into different columns with the Convert Text to Columns Wizard and make them look like a decent table.

You can access this feature through the Data Tools group in the Data Menu.

Now, let’s take a look at an example.
We have a table like the one below. We will separate the data in the A columns into the columns next to it from the delimiter points. When we look at our data, our delimiter that we will consider as delimiters are semicolons and points, and we see that they can repeat more than once.

Now let’s convert our data to the table we want using this feature.

• First, we select the range A2: A16 in which we have data.
  We choose Convert Text to Columns from the Data menu.
  In the first stage of the window, select Delimited and click Next
  We select Semicolon and Other from the Delimiters on the left.
  Select the Treat consecutive delimiters  as one option on the side and click Next
  At the last stage, we specify B2 as the Destination and click the Finish button.

As a result, we can separate the complex data on the left from the delimiter points and transform it into a table by separating it to different columns.

And we can get many other similar things done with this feature.

For example:

• We can convert numbers written as 22102019 to the Date format.
• When our thousands and decimal delimiters are written backwards as 1,000.00, we can reverse the delimiters and convert it to 000,00.
• In cases where the negative sign in numbers is at the end of the number, we can put the – (minus) sign in front of the number.

► IMPORT EXTERNAL DATA

It is important to know that Excel is not just a simple app to create tables and make calculations. In our opinion, Excel is the most inclusive and developed application in the world. It shows it is uniqueness with being sector free -anyone who sits in a desk can use this app, so to speak- and its ever-developing nature with each new version.

And the Import External Data feature allows us to connect to many database, data source and online services and import the data to Excel and show that data as Table, Pivot Table, and Pivot Chart and make analysis. It allows you to simply import data from structures like Txt, Csv, Json, XML as well as connecting to very different and broad data sources.

► EXCEL & VBA (MACRO)

Microsoft Office offers the Macro command in some of the Office packages to the users in order to automatize the routine actions.

While preparing Macros, the Visual Basic programming language that works in the background of Excel waits there. When you save anything, this programming language becomes active and translates the macro command you have prepared into the programming language. This way, when you want to run or edit the macro you’ve prepared, Excel offers you this opportunity easily.

Without a doubt, one of Excel’s most powerful features is the VBA , i.e. Macros. By using this language that comes ready in the background when Office is installed, you can codify your daily routines, weekly and monthly reports, and you can list and report your data much faster, even within seconds.

And you can prepare programs for your needs on UserForm and make everyone in your company use these programs.

You can share this article and help many other people get informed as well.

Good bye. 👍🏻

Get Excel training offer right now!