Time to upgrade your Excel skills

With Microsoft Excel’s new features being so incredibly user-friendly, there’s no excuse to remain basic. Upgrade your skills with our three tips today!

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.

 

May 5, 2020

NAME Breakfast Lunch Dinner
Mark Scrambled eggs, bacon, and home fries Pork chop and mashed sweet potatoes Shrimp and vegetable tempura with rice
Martha Avocado toast Aglio olio Salad Nicoise
Marion Pancakes and sausages Fried chicken and cheese grits Roast beef with baked potato

 

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.

This post was originally published on this site

Time to upgrade your Excel skills

With Microsoft Excel’s new features being so incredibly user-friendly, there’s no excuse to remain basic. Upgrade your skills with our three tips today!

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.

 

May 5, 2020

NAME Breakfast Lunch Dinner
Mark Scrambled eggs, bacon, and home fries Pork chop and mashed sweet potatoes Shrimp and vegetable tempura with rice
Martha Avocado toast Aglio olio Salad Nicoise
Marion Pancakes and sausages Fried chicken and cheese grits Roast beef with baked potato

 

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.

This post was originally published on this site

What’s new with Microsoft Excel?

Microsoft Excel can be used to record inventory, create simple and complex formulas, and manage data. The world’s most popular spreadsheet program is chock full of features, and with a recent upgrade, users will be able to do more with their own data. Here’s how to make the most of it.

Previous Excel upgrades include the addition of dynamic arrays and array formulas, a feature that enabled single formulas to return an array of values. Another upgrade was the Stocks and Geography function, which lets users add stock and geography data into a spreadsheet with the help of the search engine Bing. These are both useful, but Microsoft decided to add even more functionality to the program.

New data types

Excel has always been a formidable tool for storing text, numbers, and formulas and allowing users to process information out of them. Still, the data that one could put in Excel grids were limited because they were flat. Recent upgrades improve upon that limitation.

Luckily, users can now add data types to Excel, making the program more dynamic and intelligent. These data types effectively expand what information inside cells can do. In particular, cells can now contain not just text and number data, but a connected, up-to-the-minute collection of information such as currencies, cities, population, stocks, and the like. Simply put, cells can interact with charts and formulas with live data.

Data in cells can be used as a reference for an even larger collection of different data types, images, and actions. In other words, you’re no longer just typing data and writing formulas into a cell, although you can still do both. Rather, a cell can contain a specific set of information that branches out into subsets of data that you would otherwise need to type directly into the cell.

For example, if you’re creating or upgrading a customer information spreadsheet, things like transaction history, preferences, or phone numbers don’t have to be typed one by one. Adding customer-specific data types could simplify this process: entering a customer name in a cell would link to a network of information about a specific entry (i.e., customer) using a scroll-over menu, as opposed to having to enter all that information manually. This makes data input more flexible, efficient, and less prone to error resulting from copy-pasting and manual entry.

Enhancements for Power BI customers

It is, however, users of Microsoft Power BI, the company’s business intelligence program, who will greatly benefit from the upgrades. If your company uses Power BI, data published into your account will automatically link to Excel, which makes the flow of company data types into the program more seamless.

In addition, Microsoft’s data connection technology Power Query will allow users to create custom data types, while pre-configured data types (through a partnership with knowledge engine provider Wolfram Alpha) will also soon be added to let users track different types of information.

This only scratches the surface of what these Excel upgrades can do. For more Office and general productivity tips, consult our IT experts today.

This post was originally published on this site

What’s new with Microsoft Excel?

Microsoft Excel can be used to record inventory, create simple and complex formulas, and manage data. The world’s most popular spreadsheet program is chock full of features, and with a recent upgrade, users will be able to do more with their own data. Here’s how to make the most of it.

Previous Excel upgrades include the addition of dynamic arrays and array formulas, a feature that enabled single formulas to return an array of values. Another upgrade was the Stocks and Geography function, which lets users add stock and geography data into a spreadsheet with the help of the search engine Bing. These are both useful, but Microsoft decided to add even more functionality to the program.

New data types

Excel has always been a formidable tool for storing text, numbers, and formulas and allowing users to process information out of them. Still, the data that one could put in Excel grids were limited because they were flat. Recent upgrades improve upon that limitation.

Luckily, users can now add data types to Excel, making the program more dynamic and intelligent. These data types effectively expand what information inside cells can do. In particular, cells can now contain not just text and number data, but a connected, up-to-the-minute collection of information such as currencies, cities, population, stocks, and the like. Simply put, cells can interact with charts and formulas with live data.

Data in cells can be used as a reference for an even larger collection of different data types, images, and actions. In other words, you’re no longer just typing data and writing formulas into a cell, although you can still do both. Rather, a cell can contain a specific set of information that branches out into subsets of data that you would otherwise need to type directly into the cell.

For example, if you’re creating or upgrading a customer information spreadsheet, things like transaction history, preferences, or phone numbers don’t have to be typed one by one. Adding customer-specific data types could simplify this process: entering a customer name in a cell would link to a network of information about a specific entry (i.e., customer) using a scroll-over menu, as opposed to having to enter all that information manually. This makes data input more flexible, efficient, and less prone to error resulting from copy-pasting and manual entry.

Enhancements for Power BI customers

It is, however, users of Microsoft Power BI, the company’s business intelligence program, who will greatly benefit from the upgrades. If your company uses Power BI, data published into your account will automatically link to Excel, which makes the flow of company data types into the program more seamless.

In addition, Microsoft’s data connection technology Power Query will allow users to create custom data types, while pre-configured data types (through a partnership with knowledge engine provider Wolfram Alpha) will also soon be added to let users track different types of information.

This only scratches the surface of what these Excel upgrades can do. For more Office and general productivity tips, consult our IT experts today.

This post was originally published on this site