Today on Top 10 Excel features you need to know. I am a frequent visitor to StackOverflow to see what is troubling the Excel community (and other developer/analyst communities). It is nothing incredible that many users have issues due to not being aware of some of the most significant features in Excel like PivotTables, Array Formulas, Tables and other. I have seen more than once users reaching out to use VBA macros instead of much easier PivotTables/Charts. Although, I value VBA it should also be the solution of last resort compare to “native” Excel features used more common.

I have decided therefore to list some of the top 10 features regarded often as the most significant and useful to know. So let’s start with the 10 Top Excel Features…

With 1 TB of OneDrive cloud storage, you can back up, share and co-author workbooks from any device. Save time as Excel learns your patterns and organizes your data. Easily create new spreadsheets or start with templates. Use modern formulas to perform calculations. The Quick Access toolbar provides. Excel 2016 introduces fast MAXIFS and MINIFS functions. In versions earlier than Excel 2007, array formulas are often used to calculate a sum with multiple conditions. This is relatively easy to do, especially if you use the Conditional Sum Wizard in Excel, but it is often slow. Usually there are much faster ways of getting the same result. Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there's a value (e.g. Blue cars) that you're looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. Is greater than, top 10%, etc), and cell. In Excel 2007, from the 'Data' tab, click 'Data Tools', the 'What-If Analysis' icon, and then 'Goal Seek' as in the image on the right (click to enlarge). (In earlier versions of Excel, Goal Seek is under the Tools menu.). Download microsoft excel windows 10 64 bit for free. Office Tools downloads - Microsoft Office Excel by Microsoft and many more programs are available for instant and free download.

No. 1: PivotTables

You can’t call yourself an advanced Excel user without knowing about PivotTables! There is no other feature in Excel I being used more often and with success. Almost the first thing I always do when analyzing data is pivot the data in multiple manners and analyze the patterns/results. Pivots can process a large amount of data in a short period of time and are optimized in the Excel back-end for performance. This is definitely one of the 10 top Excel features!

PivotTables allow you to transform and analyze data in a structure manner. Just select a range of data (data in columns with headers) and select the ROWS, COLUMNS and VALUES for your Pivot Table! You can also create custom columns (based on formulas), summarize data by groups/rows/columns etc. There is almost no limit in the possibilities.

How to find it in Excel?
Insert->Tables->PivotTables

No. 2: Filtering and sorting data

Filtering and sorting your data is just as useful as using PivotTables. Excel is meant to transform and analyze data and filtering/sorting is one of the key elements. When provided with a table of data you will probably want to sort the data in a descending/ascending manner or filter out rows based on some features (values in certain columns). This is a must-know feature.

How to find it in Excel?
Data->Sort->Filter

No. 3: Excel Tables

If you want your data tables to be neat and structured you need to use Excel Data Tables. What do you get when using Tables in Excel? A consistent structure and formatting of your entire data table, automated copied formulas (across columns), non-repeating column headers and more. It is always good to resort to Excel Data Tables as you will have less work managing your data table and can focus on more interesting work like data transformation/analysis.

How to find it in Excel?
Insert->Tables->Table

No. 4: Conditional formatting

Analyzing/transforming data is important, but it is just as useful to be able to identify variances in a range of values using graphics like colors, bars or icons. Conditional formatting can allow you to notice patterns in data values which might not be obvious when looking at raw numbers.

How to find it in Excel?
Home->Styles->Conditional Formatting

No. 5: Lookup Excel functions

Although these functions are not exactly a separate feature they are considered to be one of the most useful and most often used when analyzing data. I can’t stress how often I stumbled upon articles/posts about these functions. These functions are also frequently used by recruiters for Excel jobs.

• VLOOKUP – search the first column of a range of cells, and then return a value from any cell on the same row of the range
• HLOOKUP – as above but for columns instead of rows
• INDEX – returns the value of an element in a table or an array, selected by provided index
• MATCH – searches for a specified item in a range of cells, and then returns the relative position of that item in the range

How to use these functions?
The VLOOKUP function will return a corresponding value from another cell of the same row of a value found in the first column of the data table.

The INDEX and MATCH functions are best to be used together (see the links section below). Why? They allow you to basically achieve the same result as the LOOKUP functions HOWEVER these are more flexible. I encourage you to read the links below.

No. 6: Array Formulas

Array Formulas are one of the greatest knowledge gaps in the Excel community in my opinion. I see so often questions which can be easily answered if someone at least made an effort to learn them. Many Excel users fall into the trap of writing a lot of custom VBA just because they are not aware or are too lazy to use a neat Array Formula.

How to use Array Functions?
Go to the links section for a decent tutorial. However, the process itself is quite simple:

1. Create a function using an Excel range e.g. A1:A10
2. Hit CTRL+SHIFT+ENTER

So easy and yet so powerful! Let’s jump into a simple example:

Excel Array Formula Example
Let’s assume we have a range of value for a certain period of time. We want to get the maximum value for dates after the 1st of March 2015. We can get this in a single Array Formula! See below.

How does it work?

See the logic? You can also multiply/divide/sum and do other cool thing with Array Formulas. See the tutorial link below.

No. 7: Data Analysis Excel Tools

All tools needed in doing basic data cleansing can be found in the Data Tools section of the Data ribbon. Working on data often? You need to know how and when to use Text to Columns, Data Validation and all the other neat tools.

How to find it in Excel?
Data->Data Tools

No. 8: Naming fields / NameManager

Naming cells/ranges comes in handy when you repeatedly reference certain cells or arrays e.g. USD/EUR currency field, interest rate used in formulas etc. This is a nice and clean way to manage all reference to those fields and allows you to easily relocate these cells or ranges.

How to set a Defined Name to an Excel cell/range?

10 Tools Of Ms Excel Function

• Click on the text field in the upper-left with the cell/range reference
• Type in Defined Name for this cell/range – it must not have whitespaces
• Hit ENTER

Now when trying to reference the cell/range in a formula simply type the new Defined Name.

How to find it in Excel?
Formulas->Defined Names

No. 9: VBA macros and recording macros

The reason VBA is placed before last on the 10 Top Excel features list is because, in my opinion, it is so often abused but users who refuse to learn well the remaining Excel features. VBA macros fills the gap of all the missing features/functions in Excel. Macros let’s you program almost anything in Excel, you name it – forms, database connectivity, analytics, web browsing etc. You can’t basically consider yourself an Excel Pro without being able to program macros in VBA. However, it is important to stress that the problem with VBA is that once learned it tends to make analysts lazy – instead of Excel Array Formulas you will see custom macros or hideous UDF-functions. VBA should be used as a tool of last resort!

Typical applications of VBA

• Cleansing/filtering/sorting/copying data
• Custom algorithms (custom analysis of data sets)
• Custom Excel UDF functions (User Defined Functions)
• Excel Forms (making custom user forms to input data or create a custom UI)

How to find it in Excel?
Developer->Code->Visual Basic
Recording macros
The other fantastic thing with Excel is that it enables you to record macros – by recording your actions in Excel and translating them into VBA code. In some cases you need not even understand the code to be able to reuse it e.g. applying custom formatting to selected cells. This is definitely a great feature on it’s own.

How to find it in Excel?
Developer->Code->Record Macro

No. 10: Microsoft Power Add-In’s

Last but not least on the 10 Top Excel features list are the PowerPivot, PowerQuery and PowerMap powerful Microsoft developed Add-Ins for Excel. Harness the power of Big Data, SQL, complex pivots and charts with these fantastic Add-ins! The PowerMap is a relatively new member of the family delivering nice bells and whistles to your Workbooks!

PowerPivot basically pumps Excel with more Analytics features by extend the PivotTable with summarization, cross-tabulation, expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications.

PowerQuery will allow you to easily harness data and access to external data sources such as files, the Web, databases etc. and more easily manipulate and cleanse data. PowerQuery enables you to process enormous data sources/tables counting millions of records (more than an Excel Worksheet can contain).

PowerMap (as quoted on the official MS site) is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. A power map lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts.

Both these tools complete one another. If you want to do Business Intelligence in Excel you need to be able to use both these Add-Ins. Microsoft seems to have great plans for them and I would anticipate that both these Add-Ins become a “native” part of Excel in the following versions of Microsoft Excel.

Where Is the Tools Menu in Microsoft Excel 2007, 2010, 2013, 2016, 2019 and 365

The ribbon interface in Excel 2007, 2010, 2013, 2016, 2019 and 365 has distributed and grouped menus and toolbars under different tabs. While this grouping is convenient, some users find it difficult to locate specific commands, earlier found in the tools menu, across different tabs.

How to Bring Back Classic Menus and Toolbars to Office 2007, 2010, 2013, 2016, 2019 and 365? Just download Classic Menu for Office 2007 or Classic Menu for Office Kutools for Excel: 120 Powerful New Features for Excel. Office Tab: Tabbed Editing and Browsing in Office, Just Like Chrome, Firefox, IE 8/9/10.

If you are a fan of the classic style interface, finding a specific function, from the former Tools menu, in the new ribbon interface can be a challenge! But with Classic Menu for Excel 2007/2010/2013, the same task can be super simple! For example,

If you have not Classic Menu for Excel 2007/2010/2013/2016/2019 installed, you have to…

Find each function by clicking on different tabs. The ribbon interface has abandoned the menus and toolbar style, and the functions in the former locations have been regrouped. For example, the Spelling function has been moved to the Review tab, and listed under the Proofing functions. The Protection function has also been moved to the Review tab, and is listed under the Changes group.

But with the Classic Menu for Excel 2007/2010/2013/2016/2019 installed, you can …

Click the Menus tab to experience the classic style interface and find the Tools menu easily. Click the arrow next to the Tools to access the familiar functions from the dropdown menu.

More Related Articles...

10 Tools Of Ms Excel Formulas

Classic Menu for Office

Brings the familiar classic menus and toolbars back to Microsoft Office 2007, 2010, 2013, 2016, 2019 and 365. You can use Office 2007/2010/2013/2016 immediately without any training. Supports all languages, and all new commands of 2007, 2010, 2013, 2016, 2019 and 365 have been added into the classic interface.

Classic Menu for Office It includes Classic Menu for Word, Excel, PowerPoint, OneNote, Outlook, Publisher, Access, InfoPath, Visio and Project 2010, 2013, 2016, 2019 and 365.

Classic Menu for Office 2007 It includes Classic Menu for Word, Excel, PowerPoint, Access and Outlook 2007.

Screen Shot of Classic Menu for Excel