Excel N Function Tips

“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

Our today’s post is about “Power Map” is by Ben Richardson who runs Acuity Training a London-based provider of Excel courses.

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.

In our last post, we explored how to use calculated fields to get customized fields and perform analysis with them.

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).

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).

Two dimensional lookup is obvious – we have a header row and a column and we want to look at the intersection of the two criteria’s.

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?

Accounts finance and marketing people are often concerned with the term “Quarter” in context of their budgeting and marketing assignments.

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

We often have sheets with similar tables and with similar layout.

Prime Numbers are numbers that are only divisible by their selves. In this small tutorial we will guide you how to find if a number is prime or not.

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

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.

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

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().

Have you ever thrown a pebble in water? You must have in your childhood! The pebble follows not a straight line but a parabolic path.

In real life, resources are scarce and wants are unlimited. This fact leads us to optimize our resources to get best out of available resources

We do create different type of charts to fulfill our charting needs – quite a few of them are related to quality control purposes.

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

Sales people often need to estimate the sales of upcoming months. They do it with a handful of techniques that uses both quantitative analysis as well as qualitative judgment.

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

Amongst the various format that number takes, time is the one of the most commonly used formats, after currency and accounting number formats.

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!

We often insert symbols in our documents. They vary from person to person and depend a lot on what we are doing.

MOD() function has variety of uses. One of the most basic one is that it is used to find the remainder from a division

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

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.

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

Networking days are important for both an employee and the employer.

To human is err, the same is true for excel. Whenever you are writing a formula, you are prone to commit error.

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

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

Shortcut to include today Just press control key and semicolon (Ctrl + ;) in any cell, enter. This will enter today’s date in to the cell.

The CONCAT, CONCATENATE and TEXTJOIN functions are popular as text combining functions, which have been designed to combine a range of strings that contain parts of text, phrases or numbers.

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

The MINIFS function is an advanced statistical function, which is only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

There are five methods you can use to look up number values in excel. These include SUMPRODUCT, SUMIF, AGGREGATE, LOOKUP and a combination of INDEX & MATCH.

Counting values in Excel using criteria is common in day to day business. For example; counting the number of failed products, overdue orders, staff in a certain department etc.

IFS function is a new function added to Excel and only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

Deleting rows are usually done by two methods as explained below.

Some may think it is a very simple thing to insert a Cent symbol (¢) in Excel. It is easy if only you know it.

Excel model can be as simple as adding up two values in cell or so complicated to cover multiple sheets or even multiple workbooks

MS Excel has been used in many diversified areas; one of them is accounts and finance.

We came across situation we have to look for values both horizontally and vertically.

When using Excel for Procurement, it is common to use it for making quotations.

SUMPRODUCT is yet another function that comes to rescue, whether we want to validate a criteria or want to sum against a list of requirements

COUNTIF() family of functions is amongst the most frequency used family of functions in Excel.

With Excel, it is not quite a rare case that numbers starts behaving like text.

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.

One of the most versatile and highly used functions is VLOOKUP. Whenever we have a table and want to quickly retrieve a value, we have to revert to VLOOKUP. Let’s take a quick dive into its use and how we can get maximum out of its use.

Most of the time when we are stuck with lookup and return something process, we revert to VLOOKUP() and occasionally HLOOKUP(), according to the situation

In our last post we have discussed various printing options – in this post we will continue with this the same topic and explore few more options that can help manage our printing tasks.

Excel has so many functions that either one of their or their combination can fulfill most of the tasks required in our work places.

Whenever you come up with a useful sheet, it is almost always essential to get it printed. Be it a simple sheet or complex business model, we can not deny the importance of a hard printed copy – it is always easier to read and record.

SUMIF() functions is among the most commonly used functions in excel. Whenever ever we have to sum against a given criteria we revert to this function, be the criteria be only one or more then one (for more then one criteria’s we use SUMIFS(), just another function of this family).

The up and down arrows on stock exchange information board is the life line of trading business.

We came across such situation we have multiple choices and we want to find the best possible combination of them, be it spending money on shopping or adopting a route that is shortest or something else.

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

MS Excel offers seven logical functions, today we will discuss how to use AND () when summing up values.

In Goal Seek, we try to reach a solution to a problem by iterating values.

The world has been growing rapidly over the past few decades. Today modern technology has completely changed our lifestyle and is adding a lot to our daily lives

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

One have seen different types of graphics that display information, ranging from simple charts, to dashboards and miniature charts like spark lines and so on, but one of them that appealed me a lot from artistic point of view is a word cloud.

Free things are so satisfying. When we get some service or learning free online, we feel so elated.

Many of the learners at **Sheetzoom.com **are willing to learn VLOOKUP function. It is an amazing and useful tools and learning to use it is much easier than you may think.

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

Excel 2013 is one of the most demanded skills today and many business owners want their employees to have the complete knowledge and skills of such tools.

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.

Businesses are all about decision making. If you are working for a firm, whether it is producing goods or providing services, you must have come across situation where you need to do the “What-If” Analysis. One of the most helpful tools in this context is “Goal Seek”.

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.

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?

Most people start learning a new tool or software when they get some new responsibility or they discover that it is the only way to shine in career.

There several ways to create in cell and that includes Excel’s built in Sparklines or using a third party Sparkline Add in, But there are ways to produce bar charts by using Excel without using these feature.

Excel is one of the tools most of us use in our lives. It is almost impossible to tell how many things you can do with Excel.

If you want to learn excel and use it for work, then you should be wondering about the common usage of this extremely innovative and powerful software.

You must have come across charts that are overloaded and you find them a pain to read. Yes these are charts that look quite pretty initially but they are read and interpreted, they become unbearable.

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

There are a lot of resources available for modeling on internet, available from books to tutorial to boot-camp trainings; this one focuses more on the concept of modeling rather then discussing details of any particular field.

**Excel sheets** can surely make your work a lot easier. Undoubtedly, excel sheets is one of the professional tools for working with information available in raw form.

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.

What is a formula? Talking generally, a formula is a way to calculate a mathematical equation. It is used to calculate both simple as well as complex equations.

Lots of people are looking to learn Excel these days. It is one of the most demanded skills in all sorts of businesses, no matter whether they are small or large.

A spreadsheet is an electronic document formed by the combination of rows and columns marked with a grid. **Excel **is a matrix and powerful platform and a massive program.

The relationship listbox template has been used exactly zero times. One can build their classes from start along with names which actually reflect the objects of the business they are aimed at representing.

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

The “Show Values As option” can be used for performing several calculations in **Excel Pivot tables**

Measures is amongst the most important and highly powerful features in **Power Pivot**. Measures are actually the calculations or formulas you add to the Pivot Table. Below is an example of it.

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.

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

What formula will tell you that a specific cell contains a specific text?

**Excel** has pivot tables that offer different types of calculations. You can start off with it by selecting the ‘Show Values As’ option. Then you can get the calculation of the **‘Percent of Row Total’** as well.

**Excel **is a life saver at work and there is always a **free online training for Excel** in the internet. At Sheetzoom, we offer free excel training using Excel Online and you can learn by doing. The key to Excel learning is actually solving real problems with Excel.

**INDIRECT Function** yields a reference to a range. The range being referred can be a named range, a range of cells or can be a cell.

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

Majority of people working with computer systems love shortcuts on keyboard. Few of the shortcuts which are custom made can help you to clean Undo Stack, which is quite painful.

We often see numbers stored in Excel as text which leads to wrong calculations, especially while using the cells in the functions i.e.

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

**Pivot tables** include so many essential calculations in the **SHOW VALUES AS** option.

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.

The MATCH function of Excel 2016 permits users to select the position of an object in a range instead of selecting the entire actual item

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

The INDIRECT function is a powerful function as it allows you to carry out different tasks that come in very handy.

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

Do you need to create new workbooks all the time and then making similar changes to all of them?

Grouping of numbers can be done for creating a frequency distribution table in a Pivot Table.

You are working on Excel and you need to submit work within an hour.

There are many useful functions for Pivot Tables that are available in Microsoft Excel.

Both date as well as time is stored in the form of numbers in Excel. This allows the user to utilize these dates as well as times for the purpose of calculations.

In some scenarios, you desire to lock cells so that no changes can be made in the cells by other people in Excel.

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.

Flash fill is one of the features introduced in one of the latest releases Excel 2013.

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 2016 introduces a lot of new Charts for us to use in presentations.**

Excel has recently introduced many new charts in its latest version.

**The RANDBETWEEN function is available for use in Microsoft Excel. The function is used to generate randomized dates between a specified start date and end date. **

Working with **Microsoft Excel** to produce multiple tables that are linked together is easy.

Microsoft Excel Pivot Tables have many functions available through the option of Show Values As. One particular calculation can be used to display the Parent Row Total Percentage.

Have you ever used the MOD function in Excel? Well, the function actually performs modulo operation.

Many of the people find it difficult to extract the URLs from the list of text. It is as easy as anything. There are two different ways of doing it.

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

The Office 365 subscription comes with new Power Pivot updates for the features in the 2016 version of Microsoft Excel.

Among the numerous new charts available on the new Excel 2016 is the Box and Whisker Chart. This chart was originally created by John Tukey in the 1970s

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.

It is possible to apply step chart if you intend to monitor changes that occur at different times. This could include interest rates, tax rate, petrol and milk products.

**Power Pivot** is basically an **Excel** tool that was first introduced to the public in the year 2010. What exactly is meant by **Power Pivot**?

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.

Measures is a very powerful and vital feature in **Power Pivot**. Measures are fields that have been calculated in the 2013 version of **Microsoft Excel** and have been included in a Pivot Table.

If you are wondering what the top ten formulas are, then you should really consider acknowledging the fact that there is no standard list of the perfect ten formulas for each individual.

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.

Normally, the VLOOKUP function lookup function is normally not sensitive to case

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

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.

Suppose you want to randomize a list, for this you have to have a list of random (or shuffled) numbers, which can be used to randomize the list you want.

If you are wondering if it is possible to create sequence numbers from clustered values, then you should definitely consider reading ahead to find out more

Based on surveys, we have compiled a list of the most popular Excel shortcuts you will find.

Here you will find some very useful shortcuts that you can use throughout your term of using Excel. Each shortcut has been given for each day of the weekday.

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.

**COUNTIF()** function is used for counting cells with unique values in a range that meet a certain criterion, or condition.

At first instance, you may wonder why we would need to remove spaces in Excel. If you have unnecessary spaces it may prevent working of some functions, filtering etc.

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.

The CONCAT function was recently introduced by replacing CONCATANETE function and the CONCAT is only available with the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

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.

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.

MAXIFS function is considered to be one of the advanced statistical functions available in Excel.

The SWITCH function is a highly useful function which can be used for various applications with conditional splitting logic.

The SWITCH function is a highly useful function which can be used for various applications. This is newly introduced to Excel and it is only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

You know the **functions** and **formulas** but need to practive your **Excel skills**? Or do you need **Excel Practice Tests** online? Well you are at the right place. We have free excel practice tests where you can sharpen your skill. These are not multiple choice questions in fact you learn by doing.

Excel is best learned by actually doing stuff. You can open the Excel software desktop or online, try writing formulas.

You may need type degree symbol for number of uses, for example to show degree values, Celsius, Fahrenheit etc. However, this symbol cannot be directly entered from the key board.

The COUNTA function is usually used for counting the non-empty cells in a given cell range.

In Excel, spell checker is a tool that used to recognize errors incorrectly-spelled words. Which is highly useful since you don’t have to manually check all the words that you typed in excel.

**COUNTIF()** function is used for counting cells with unique values in a range that meet a certain criterion, or condition.

The SUMIF function is used to conditionally sum values based on certain criteria. Another version of that function is **SUMIFS**

**COUNTIF()** function is used for counting cells with unique values in a range that meet a certain criterion, or condition.

The COUNTIFS() function is an extended version of the COUNTIF() function which is used to conditionally count items/ cells based on certain criteria.

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.

VLOOKUP stands for Vertical Lookup. Learning vlookup is very easy but let’s first understand how VLOOKUP works?

Check out the What's new in Excel 2016 for Windows(Infographic) to know about:

Format numbers according to the Indian number formatting so that 100,000 displays as 1,00,000

This new function allow to add handwritten equations to text, so you can add mathematical equations more easily to excel documents. The sketches of the equations can be made using a mouse or a digital/fingers.

The SUMIF function is used to conditionally sum values based on a single criteria. The Syntax of this SUMIF function can be explained as follows:

**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.

Inserting rows in between selected rows, is one of the most necessary and important functions we require when working in excel worksheets.

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.

Merging is a useful function in MS Excel which is used more often to make title/larger cells or also to combine

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

Conditional Formatting is a widely used tool of Excel that provides pre-determined formatting to be applied to a cell or range of cells. The formatting may depend on the cell value or the content of it

Using UPPER(), LOWER() and PROPER() functions with NESTED IF().

Using IF(), ISTEXT(), ISNUMBER() and ISBLANK() functions.

Using the MIN(), MAX(), COUNT() and AVERAGE() functions on a list