Level up your Excel skills with these tips

Microsoft Excel’s new features now make it easier than ever to process data and present information visually. Level up your spreadsheets with the tips below:

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 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 visualization 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 another variable that continuously increases at a regular rate
• 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
• 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

Intelligent answers for questions about your data

Thanks to natural language processing, users are able to ask a specific question or make a visualization 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 thereafter revise the label of that chart to “Total sales for underwear.”

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. Plus, 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 the name “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 that the user 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)

Make data extraction easier 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 May 5, 2020. The names of these three individuals are in Column A, while what they had 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 those of 20,000 other people, in a list that’s randomly ordered, in a table spanning the year 2020. Instead of driving yourself crazy, you can retrieve that information efficiently through Excel’s XLOOKUP function.

Extracting data that corresponds to other pieces of data is what lookup functions are for. The very first to be rolled out 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 April 2. 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 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.

Excel is now more user-friendly than ever before. To take full advantage of the best features Excel has to offer your business, contact us today.