For those who work with data, Excel is a lifesaver. Its functions range from simple searches to complex formulas that allow you to sort, analyze, and visualize large sets of information. The wizards at Microsoft are constantly working to make Excel more user-friendly, and the newest Excel 365 is no exception. With upgrades, new functions, and cool artificial intelligence, it makes working with data easier than ever before. If you’re not in the know, check out these 3 tips for making the most of Excel 365 for your business!
Get Inspired with Ideas
We’ve all been there. You’re neck-deep in an excel sheet and need to share a table of data in a certain way, but you’re not sure which chart to choose! Ditch the old-school method of trying them one at a time and let Excel’s artificial intelligence (AI) feature do the work for you with their exciting new Ideas feature.
Trying it out is easy – just click on a cell in your data table and click the ideas button located on the home tab. Excel will then show you recommendations for various charts and diagrams that fit your data set!
To save even more time, here’s a refresher on the most common Excel charts:
Line Chart – This chart shows trends in data over time (or another constant variable). It’s good for finding increases, decreases, and fluctuations in value in a given period.
Column/Bar Chart – This chart is used to compare categories of data by showing their value in visual quantities, by column (height) or bar (length). It allows you to compare numerical values quickly across a variety of data sets.
Pie Chart – This chart allows you to look at competing parts of a data set in a circular formation. Each portion of the chart is relative to the size of that category in the group as a whole. This chart is used to determine sectors in a data set, such as the percentage of obese children in a community.
Scatter Diagram – Also known as an XY chart, a scatter diagram is commonly used for scientific data. Its purpose is to show a relationship between 2 variables by comparing them on horizontal and vertical axes.
Excel is constantly working to make their software easier for users, and the Ideas function is no different. In the works is a voice-command option that uses natural language processing to allow users to ask questions to Ideas and receive visual representations of their data that fit their needs. For example, you could ask how quickly your company’s profits increased in quarter 3, and Excel will show a line graph of your company data for those months.
Save Keystrokes with Dynamic Array Formulas
In the fall of 2018, Excel launched an exciting new feature known as “dynamic array formulas”. The updated array formulas allow you to obtain multiple results across a range of cells based on a single formula put in one cell. This behavior is known as “spilling”, and it’s a huge benefit to users who were previously limited to a “one formula, one cell” rule that forced them to nest functions to sort and analyze complex data sets.
The best part of the new feature is what makes it dynamic. As with single-cell formulas, the dynamic array formulas give accurate, updated results as the data is changed – but now, it’s from anywhere in the source material. That means your results evolve with your work, saving you time and headaches along the way.
Dynamic array formulas are particularly useful when using Excel’s unique function to identify and extract specific values from a data set. But Excel didn’t stop there – they released several functions updated with dynamic array formulas that make it easier than ever to analyze data:
FILTER – Filter data for specific matching records
RANDARRAY – Generates a table of random numbers between specified values
SEQUENCE – Generates a table of sequential numbers from a specified value and at a specified increment
SORT – Sorts values by column in increasing or decreasing values
XMATCH – Replaces the Match function to locate specified values in a cell range
XLOOKUP – Replaces the VLOOKUP and HLOOKUP functions to locate specified values in a cell range (see more below)
Dynamic array formulas are only available in Excel 365. For previous versions, the legacy CSE formula (ctrl+shft+enter) is the closest option.
Extract Data Faster with XLOOKUP
Excel’s LOOKUP functions allow users to extract specific data from larger sets. In previous versions, users had two options: VLOOKUP, which analyzed data by rows from left to right, or HLOOKUP, which analyzed data by columns from top to bottom.
XLOOKUP is Excel’s replacement for the previous lookup functions, and it’s a good one. Modern and flexible, the new function incorporates dynamic array formulas and allows users to search horizontally and vertically across a cell range at the same time. It can also return multiple results, meaning users can locate an entire row of data rather than being limited to a single cell.
To illustrate, let’s say you have a table of employee information. You need to find an employee’s ID number and department by their last name.
| FIRST NAME | LAST NAME | EMPLOYEE ID | DEPARTMENT |
| Edith | Wharton | 1234 | Marketing |
| Andrew | Smith | 1876 | Finance |
| Susan
Bob |
Bigby
Fellows |
5672
8732 |
Sales
Operations |
With previous versions of lookup, you would need to run the VLOOKUP function twice – once for employee ID, and once for Department. With the new XLOOPUP function, a single formula can return the entire row to make data extraction far easier.
XLOOKUP has other benefits, too! It now defaults to an exact match of your specifications whereas VLOOKUP defaulted to an approximate match. It can also run reverse searches, locate data to the right or left of lookup values, and works with array formulas to run complex searches. It can’t yet return multiple matches (so if two employees have the same last name, you’ll need to choose between the two), but you can integrate it with other functions to do so.
For more tips on IT software or help with your business technology, contact us today!
RELATED:
5 Overlooked Causes of a Slow Network
About
is the technology leader on the Gulf Coast and is comprised of four divisions: Information Technology, Web Design & Digital Marketing, Office Equipment and Business Consulting. Together these divisions help local businesses exceed expectations and allow them to group to their full potential while minimizing risks. To learn more about , visit bistechnologygroup.com.
You may reach out to us at:
Phone: 251-405-2527
Email: plong@askbis.com
