Excel Data Function Tips

Understanding how MS Excel Query Works

“Query” in MS Excel has same meaning as it has in our daily lives. It inquiries from our database and return some results or information

Using Pivot Charts For Displaying Data

Conventional charts are mostly used for displaying rather “static” data from excel sheets – i.e. if you have a table where you have manually entered data and you want to make a chart, go for the conventional excel chart.

Using Pivot Table Calculated Fields

Pivot table are a great way to analyses the data for an excel user. Most of the times, the pivot tables are produced using existing fields (or variables).

Creating Histogram With Analysis Tool Pack

One of the best ways to find how your data is behaving is to plot a histogram. Creating histogram is amongst the firs step we take to analyze the data as it outlines how the data is distributed, details about the skew-ness and kurtosis (described later in this section). 

Averaging The 5 Lowest Values In Your Data!

Let’s assume you are a math’s teacher interested in finding the average of the math’s score of last five students – he is interested in finding why they are performing poor. How will he proceed? 

Using Substitute Function To Convert Values With Comma To Numbers

We encounter this problem of numbers behaving as text with commas when we import data from some other software in to excel sheet. 

Producing Panel Charts In Excel

Data bars are amongst one of the many feature excel has for presenting data.

Using SUMPRODUCT() To Validate Multiple Criteria In Columns

We have studied and used SUMIFS() and SUMPRODUCTS() function with their appropriates use. Just to recall, SUMIFS() work great when our data is in the shape of list or is in column.

Can You Convert A Number Present As String To A Proper Number?

There are times when you bring data from other software to excel sheets and find that some of the numbers are formatted as text

Getting Data From A Pivot Table – Using Getpivotdata()

If you have a large pivot and want to create a report based on that pivot, it is time to revert to a function dedicated for it – GETPIVOTDATA().

Finding An Equation That Fits Your Data…

Whenever we have data, we are interested in getting some meaningful insights from it.

Introduction To Slicers In Excel 2010

You must have used filters in Excel! Whether you are using a table or have a list, whenever you have data and want to search for certain information

Extracting Meaningful Information From Raw Data – An Example Of Using Sales Data To Get Meaningful Insights

If you are running a business and wants to see how much do you sale and what is the breakup and effect of each product on your profits, this post is for you!

How To Calculate Average Absolute Difference For You Data With Negative Value!

A recent question on a website requested for calculating the average of absolute differences for a year-over-data.

How To Sum Every Nth Row In Excel

It is not uncommon that we want to sum data on the basis of certain criteria – on the basis of weeks, months, every tenth day and so on.

How To Plot For The Start And End Point On The Graph

One of the Quora users recently asked a question regarding how to plot lines at the start and the end of a data series.

How To VLOOKUP Data For An Entire Month

Consider a case where you have a table like below and want to fetch the data for a single month.

10 Quick Time-Saving Excel Shortcuts & Mouse Tricks For Marketers

It is possible to copy a pattern of data in a very simple way. The data can be either numbers, text, dates or formatting. 

How To Calculate Weighted Average In Excel

The average value of number of cells or a data range in Excel can easily be calculated using AVERAGE() formula.

Removing Duplicates In Excel

We frequently encounter data while working with lists. It is a common problem caused by human error as well as duplication of data due to common practices – copy and paste procedure for consolidation of data.

Excel Training Online

Excel is an electronic spreadsheet. It is used to stire large amount of numerical data, perform numerous calculations, manage and update them.

Using Data Table To Perform Analysis

MS Excel provides us with various tools to analyze the effect of change in variable on final output.

Using Wild Cards To Count Last Names – A Quick Tutorial

Whenever we have data, we want to extract meaningful information from it.

How To Learn Ms Excel 2010

Excel is one of the mostly used Microsoft office tools. it is used for storing financial data, employee and student records, calculation, graphs, pivot tables and many more.

Free Training Of Excel To Make Your Life Easier

What the companies are expecting from the employee these days is that they should know everything about Excel ribbons, capability to enter and format data, the complete use of formulas to calculate total and stuff, highlighting the main data, developing reports and charts, formatting data and knowing the details about shortcuts and tricks to enhance the productivity.

Displaying Large Data Sets With Sparkline In Excel

Our paper is designed to accommodate two-dimensional charts, or at best 3 dimensional charts. But those charts are produced at the expense of understandability and appearance of them. What if you want to produce so many to them to accommodate cross-tabbed data?

Online Training For Excel

A spreadsheet is a sheet consisting of rows and columns which intersect with each other to form cell. Data are entered in each cell for calculation, storage or manipulation. Excel is collection of electronic spreadsheets

Generating Random Data In 5 Minutes

When we are developing a model or trying to test some formula, we need data and when we don’t have one, we have to create it to suit our needs. This is frequently called dummy data.

Reversing Rows with Power Query Feature In Excel

 

Power Query allows the execution of several steps in series for the basic purpose of transformation of data present in Excel.

Using Excel’s TRIM Formula To Clean Data

The TRIM formula introduced by Microsoft Excel allows us to remove all the unwanted spaces that are present within a text, other than any singular spaces in between different words.

Excel Charts And Logarithmic Scales

Graphing collected data makes the interpretation of the accumulated information much easier to read, comprehend, and share with others.

Using Excel’s Multiple Criteria In VLOOKUP Function

Microsoft Excel’s VLOOKUP function is a popular feature amongst office personnel and data processor positions.

Combine Data With TEXTJOIN

Included in the group of six functions released by Excel 2016 is a very valuable function; TEXTJOIN. 

Extracting Data With Excel’s LEFT Formula

Left formula? What does it returns? Well, it gives back the first character in a string. The amount of characters is given as an input and result is extracted on basis of that.

Group Rows And Get Counts Using Power Query

Power Query is a very strong tool available in Excel. It allows you to perform different types of transformations on your data.

Power Pivot Tables In Microsoft Excel

Microsoft Excel has many powerful functions which are very useful in manipulating data into meaningful information for all kinds of purposes.

Replace Values Using Power Query

Power Quey is a strong command used in Microsoft Excel. It allows you to perform different steps in order to transform the data within your Excel sheets.

Transpose Data In Excel Quickly

In case you need to transpose a dataset in Excel, that is, you want to convert the columns into rows and vice versa, it is very hard to do that manually.

Microsoft Excel: Using Conditional Formatting To Make Heat Map

A data set’s comparative view can easily be represented visually with the aid of a heat map

Using Excel To Create Histogram

In business, several tools are used to analyze data. One of such tools is the histogram. The histogram is a chart, which has chart columns that signify how frequent a variable is present.

Easily Transforming and Shaping Data in Microsoft Excel 2016 Version

Raw data in most cases, are often in a form that is of no major benefits to the users due to the fact that they are messy.

Excel And Big Data

Excel does not only have the ability to handle small data but also very big data as well. Big data can be described as data that has a high variety, high velocity or high volume. High variety entails huge shape of data which changes quickly over a period of time.

Use the CLEAN Formula and Clear Excel’s Unprintable Data

The CLEAN formula assists you in erasing and removing all characters that cannot be printed from the text.

How To Use Sparkline

The Sparkline in Excel is a tiny chart, which can be included within the background a cell. This is used to provide visual representation of data, showing the variations, minimum/ maximum values and data trends.

How to Set Up Dashboards

If you are wondering what exactly is a dashboard, then in simple words a dashboard is basically a report of data that allows the manager or a business analyst to gain an overview of what the business is doing and assist them in making the right decisions.

How to Revert Back to Original Sort

The sorting facility is an important and highly useful feature provided in Excel. Which can be used to handle and retrieve data from large databases.

How to Enable Conditional Data Entry in Excel using Data Validation

Sometimes when you are creating worksheets with data inputs, you may want to restrict the data input format (i.e. numbers, decimals, date, text etc.) and their range.

Outline data in Excel

Outlining data is a useful fuanction provided in Excel, which facilitate better organizing of data tables. It will also enable gropuing of simmilar data and collapsing.

How to Count Cells that Contain Text Strings

When you have a large data set with text strings, you may need to find or count data based on their text content. You may know to do this with numerical values, but also know that this can be done to ant cells that contain text strings.

How to use Vlookup with Exact Match

Vlookup can be considered as one of the most beneficial functions offered in Microsoft Excel, which has become much useful in data analysis and data processing work.

How to Make a Drop-Down List in Excel

Drop-down list is the ideal option for selecting an item from a list. So that the user does not have to type it and can select from the available list. It also then allows not entering invalid data.

How to Unhide Columns in Excel

When you have some data columns that you don’t want to display can be hidden from the sheet.