INDEX MATCH is one of several lookup formulas available in Excel. It has certain features that make it superior to VLOOKUP in many situations
Category: Smart Excel Tips
Advanced PivotTables: Combining Data from Multiple Sheets
When you want to create a PivotTable, what do you do if you have data in different worksheets? If you’re using Excel 2013, you have a streamlined method of doing this. There’s a technique called the Data Model, and it uses data relationships the way a database does. In this tutorial, I’ll show you everything […]
Why Do You Need Formulas and Functions?
If you are an office administrator, you probably use Excel. Ditto if you are a salesman. If you are a financial analyst, of course, you need Excel. For these and many other professions, working with spreadsheets is an essential tool in the daily routine.
How to Create a Gantt Chart in Excel
I like Excel. I use it daily. In companies and homes all over the world, people use Excel for everything – from a makeshift calculator to tracking massive projects. Given the software’s omnipresence, it’s no wonder so many people try to repurpose it for tasks that Excel was never meant to do. I’ll show you how to create an Excel Gantt chart.
How to Remove Duplicate Rows in Excel
When you are working with spreadsheets in Microsoft Excel and accidentally copy rows, or if you are making a composite spreadsheet of several others, you will encounter duplicate rows which you need to delete. This can be a very mindless, repetitive, time consuming task, but there are several tricks that make it simpler.
Excel Formulas: 10 Formulas That Helped Me Keep My Job
Don’t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.
How to Create Advanced Microsoft Excel Spreadsheets
Looking to become an Excel power user? Excel has a number of features that will make it easier for you and your colleagues to enter data into your spreadsheets. If you’re developing a spreadsheet that you’ll use over and over again, inserting a spin button or scrollbar will allow you to choose from a predefined […]
How to Use Flash Fill in Excel 2013
Excel 2013’s new Flash Fill feature anticipates your formatting and data requirements and accommodates them on the fly. Excel does so by learning and recognizing patterns and then uses its auto-complete functionality to finish the work you started. Flash Fill encounters, interprets, and responds – you don’t have to do a thing but understand what […]
Center Excel Headings Without Merging Cells
If you’ve ever tangled with merged cells in your Excel spreadsheets, you probably know about the havoc they can wreak on things like sorting or filtering. Beyond that merged cells can also make the simple act of selecting a range of cells difficult. I know that button for centering across multiple columns is sitting right up there on […]
Learn Using “Quick Analysis” in Excel 2013
Microsoft’s spreadsheet program, Excel, is one of the most useful tools that any manager’s disposal. This dynamic tool can be employed in a multitude of ways, from tracking time, to finances and even sales. One of Excel’s most useful functions is the ability to develop graphs and charts from information which can then be easily analyzed. If you use Excel 2013, you have Quick Analysis – a powerful feature that allows you to quickly analyze data.
Below is an overview of the Quick Analysis tool in Microsoft Excel 2013.
What is Quick Analysis?
In older versions of Excel, if you wanted to visually analyze your data, you would have to first create a chart or graph and then format it. This could take some time and also lead to mistakes, not to mention the fact that it can be a challenge to pick the correct type of chart or graph for your data type.
To make it easier to visualize your data, the Quick Analysis tool was introduced with Excel 2013. This feature allows users to instantly create charts and graphs with the click of a button. It even suggests a visualization method that best fits your data, making picking the correct way to show the information far easier. You can even add miniature graphs to single cells – called Sparklines – that allow you to quickly spot trends without having to look at a full graph.
How to use Quick Analysis
Enter your data in a spreadsheet, and if need be include column headings.
Select the data you would like to visualize.
Press Ctrl + Q to open the Quick Analysis gallery. You can also select this by hovering your mouse over the bottom-right corner of the selected cells and clicking the icon that pops up.
Select the tab you want e.g., Charts for suggested charts. Note: The options available to you will change based on the type of data you have selected.
Hover your mouse over an option to see a preview of the selected visualization.
Click on your choice to create the visualization in a new worksheet.
Which Quick Analysis is best?
When you open the Quick Analysis gallery you will notice that you have five different options:
Formatting – Lets you adjust the data you have highlighted. You can set a color for the data to set it apart and quickly see both high and low values, or even remove formatting altogether.
Charts – Lets you select different charts and graphs based on the data you have selected. This can include pie charts, bar graphs, line charts, etc. If you press More Charts you will be able to select from a larger list.
Totals – Lets you calculate numbers in rows or columns. Some useful options include Running Total which keeps a total even when you add more data, and Sum which will total either a column, row or the total sheet. Be sure to pay attention to the colored dots in the icon as they indicate whether a row or column will be calculated.
Tables – Allows you to create tables or sub-tables using just the selected data. You can also create a pivot table. You should also be able to select More if the type of table you need isn’t there.
Sparklines – Allows you to add small charts beside your data. These one to two cell visualizations are great for quickly identifying trends within your data.
If you are looking to learn more about Excel 2013 and how you can leverage it in your business, contact us today.
Courtesy: techadvisory.org