Best Microsoft Excel Bloggers

Wednesday, January 26, 2022

Quick Analysis Icon


Quick Analysis Menu

If you select a contiguous set of data you will see a small icon display in the bottom right hand corner. If you click the icon, the Quick Analysis menu will display.

This menu allows you to quickly Format, Create Charts, Totals, Tables and so much more. Simply hover your mouse over each option and your data will preview the resulting display.

For example, if you have your data selected and then have your mouse hover over Data Bars in the Formatting option, you will see Data bars display in your worksheet if it finds numbers.

 Move the mouse over to icon sets and icons will display. 

Select Charts, from the top of the Quick Analysis menu, and you can select a chart type and have a chart instantly previewed for you.

Totals allow you to see different summarization methods of your data – Total, Average, Running Total and More.

If you select Tables, you can quickly view a pivot table.

This menu is available in Excel 2013 and higher.
If for some reason, you do not have this menu and you have the Excel 2013 or higher, go to File>Options and look in the General Tab to make sure it is checked.
Alternatively, you can try CTRL+Q to turn it on or off.

Friday, July 24, 2020

SWITCH - New Excel Function

SWITCH  is the newest function that Excel introduced in Excel 2019 and Microsoft 365.
In my mind, it is a bit of a cross between an IF function and a VLOOKUP function.

compares one value against a list of values and then returns the first match it finds. The list of values can be referenced or typed in.

 In the screenshot below, I have 3 employees and their Employee Levels. Every Employee Level has a starting salary. For example, all Employee Level A personnel start out at $35,000 while Employee Level B 's salary starts at $40,000.

In this example, I am telling Excel to look at the value in cell B2 and see if it is equal to an A. If it is equal to an A, I want it to return a value of $35000 to cell C2. If it is not an A, I want Excel to look and see if it is a B. If it is a B then Excel will return the corresponding value of $40000. I hard coded these values in but you could have used absolute cell references as well.  You can also set a default value to use if there is no match.

If you are handy with text functions then incorporating SWITCH into them should be useful when doing an inventory list of parts and part numbers.

Ms. Excel- Resident Excel Geek