Tech Tip Tuesday - 3 New Microsoft Excel Features

Microsoft Excel’s new features now make it easier than ever to process data and present information visually. Help yourself to our three useful tips below.

1. Take advantage of Ideas in Excel

If you’re not sure which type of chart best tells a particular story out of a table of data, don’t worry - Excel’s AI (artificial intelligence) has got your back. Just click any cell in a data range, then go to the Home tab and click the Ideas button. A task pane will appear, showing recommendations for what data visualisation you can use.

Here’s a quick reference for what some of the most popular charts are for:

• Column/Bar chart – compares categories with one another by depicting their numerical standing in terms of column height (or bar length, in the case of bar charts) side by side (think of lining children up and looking at how tall they are).
• Line chart – shows how data changes over time or other variables that continuously increase at a regular rate (think of someone tracking their weight weekly during the entirety of their three-month diet).
• Pie chart – sets items as being parts of the same whole to depict and compare how much each contributes to or partakes of that whole (think of a statistician showing a town’s composition by race).
• Scatter diagram – sets two variables as axes on a Cartesian plane and plots data as points on that plane to show whether a relationship exists between the two variables, and if so, what kind of relationship they have with one another (think of recording the temperature at noon every day and noting sales of bottled water to see if increasing temperatures lead to increasing sales).

Coming soon: Intelligent answers for questions about your data

Thanks to Natural Language Processing (NLP), users will soon be able to ask a specific question or make a visualisation request to Ideas, and Ideas will respond by providing a chart that answers that question or fulfills that request. For instance, if you have sales data for shirts, sweaters, boxers, briefs, jeans, and socks, and you ask for “total sales for boxers, briefs, and socks,” Ideas will show you a pie chart showing how much those three items contributed to their total sales. You can then revise the label of that chart into “Total sales for underwear.”

2. Save time by using dynamic array formulas

For the longest time, Excel users had to enter one formula in a cell to have something returned in that particular cell only. If, for instance, you opted to use formulas (instead of the Remove duplicates command) to obtain unique values from a list containing repeating names, you’d have had to nest all sorts of functions that only increase in complexity for every succeeding cell that you use. This is no longer the case, thanks to dynamic array formulas.

Simply put, dynamic array formulas let users obtain multiple results that “spill” over multiple cells by just executing a single formula. That’s the array part, but why is it called “dynamic?” Well, just like how a single-cell formula result changes when that formula’s source reference changes, the results of the entire array also stay in sync with the changes in the source reference.

To illustrate, let’s say that you use the Unique function to obtain a list of non-repeating names from a list that mentioned “Eric” a lot. The resulting array will be a list that mentions “Eric” only once, but if all instances of “Eric” were deleted from the source list, then “Eric” will be taken out of the results array.

Besides Unique, these are some of the other functions that showcase the dynamic array feature:

• Filter – extracts all records that match the criteria you set (e.g., a list of names of people with green eyes).
• Randarray – generates a table full of random whole numbers or decimals between two values that are provided by the user.
• Sequence – creates a table full of numbers that begin at a specified value and increase by an increment set by the user.
• Sort – extracts unique values from an array and rearranges them into a new array according to the ascending or descending order of a chosen column index (e.g., a list of billionaires and their respective net worth, sorted from lowest to highest net worth).

3. Make data extraction a breeze with XLOOKUP

Let’s say you have a table that records the meals that Mark, Martha, and Marion ate for breakfast, lunch, and dinner on January 4, 2020. People’s names are in Column A, while meal names for breakfast, lunch, and dinner are in Columns B, C, and D respectively.

Finding out what Mark ate for lunch is easy enough to do manually, but now imagine his name among 20,000 other people, in a list that’s randomly ordered, in a table spanning the entire month of January. Instead of driving yourself crazy, do it programmatically via Excel’s XLOOKUP function.

Extracting data that corresponds to other pieces of data is what lookup functions are for. The very first was Vertical Lookup or VLOOKUP, a function that goes row by row to look from left to right to retrieve your desired information. Next came Horizontal Lookup or HLOOKUP, which did the same thing, except it went column by column to look from top to bottom.

The latest and best iteration of the lookup function is XLOOKUP. It combines both VLOOKUP and HLOOKUP and improves them by being able to go right to left and bottom to top as well.

Let’s go back to our 2020 meals table example above. With XLOOKUP, you can find out who ate roast beef with baked potato on January 1. However, if many people ate that meal that day, you’ll only be able to retrieve either the first or last match in the table depending on whether you specify that the search be done from first to last or in reverse order. Returning multiple matches is possible, but only by integrating other functions with XLOOKUP.

Despite XLOOKUP’s inability to return multiple matches on its own, it can, however, return multiple values from a single match (a la dynamic array). To illustrate, let’s limit our example table to just one day, and we want to extract what Marion ate for breakfast, lunch, and dinner. If you choose to use the older VLOOKUP function, you have to perform it three times — once for every meal. With XLOOKUP, you only have to do it once. This is such a huge time-saver, especially if you want to extract entire rows or columns of data from your source table.

Need help with Microsoft Excel?

Excel is now more user-friendly than ever before. Our experienced Business Technology Managers (BTMs) are available to assist you roll out an Office 365 or Excel project, or to ensure that you have an up-to-date and connected productivity suite. If you need help with yours, give us a call on 1300 307 907.