Understanding How HYPERLINK Works In Excel
Excel has a built in function to create hyperlinks – named HYPERLINK(). This function is useful when you want the user to be redirected to some other place for example a webpage or a document or some place within the excel sheet.
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 “Calculated Items” For Analyzing A Pivot Table
In our last post, we explored how to use calculated fields to get customized fields and perform analysis with them.
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).
Performing The Three Dimensional Lookup
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.
How To Find The Quarter Of Year
Accounts finance and marketing people are often concerned with the term “Quarter” in context of their budgeting and marketing assignments.
How To VLOOKUP From Tables In Different Sheets In Excel
We often have sheets with similar tables and with similar layout.
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
.png "Getting Data From A Pivot Table – Using Getpivotdata()")
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().
Understanding Optimization With Excel 2010
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
Developing And X And R Chart For Quality Control
We do create different type of charts to fulfill our charting needs – quite a few of them are related to quality control purposes.
Using Moving Averages For Forecasting
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.
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
Understanding Time Format And Operation On Time In Excel 2010
Amongst the various format that number takes, time is the one of the most commonly used formats, after currency and accounting number formats.
.png "Extracting Meaningful Information From Raw Data – An Example Of Using Sales Data To Get Meaningful Insights")
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!
A Short Tip - How To Insert Symbols Quickly
We often insert symbols in our documents. They vary from person to person and depend a lot on what we are doing.
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.
Calculating Workdays With Different Methods
Networking days are important for both an employee and the employer.
5 Ways To Handle Or Prevent Errors In Excel
To human is err, the same is true for excel. Whenever you are writing a formula, you are prone to commit error.
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.
14 Simple Excel Shortcuts, Tips & Tricks
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.
Concat VS. Concatenate VS. Textjoin
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.
How To Use MINIFS Function In Excel
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).
Looking Up Number Values
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.
How To Use IFS Function In Excel
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).
Basic Tax Rate Calculation
MS Excel has been used in many diversified areas; one of them is accounts and finance.
Two Way Lookup With Vlookup
We came across situation we have to look for values both horizontally and vertically.
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 Vlookup
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.
Using Index-Match Combination To Ease Up Lookup Process
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
More On Printing Options – Making Things More Reading In Excel
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 Functions And Text Functions
Excel has so many functions that either one of their or their combination can fulfill most of the tasks required in our work places.
Printing Excel Sheets
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.
Understanding And Doing Better With SUMIF Function
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).
How To Show Changing Performance With Stock Exchange Tickers!
The up and down arrows on stock exchange information board is the life line of trading business.
Using Solver To Find The Most Profitable Mix Of Products
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 Training Online
Excel is an electronic spreadsheet. It is used to stire large amount of numerical data, perform numerous calculations, manage and update them.
How To Sum Using Logical Functions - An Example Of Using AND () And OR() In Excel
MS Excel offers seven logical functions, today we will discuss how to use AND () when summing up values.
Understanding Scenario Manager
In Goal Seek, we try to reach a solution to a problem by iterating values.
Excel Training
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
Creating Word Clouds In Excel!
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.
Example Vlookup
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.
How To Learn Excel 2013
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.
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?
How To Build In Cell Charts To Display Information
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.
How To Use 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.
Make Your Charts Easier to Read – 4 Tips
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.
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.
How To Learn Excel For Free?
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.
How To Learn Excel? Start And Master With These Resources
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.
COUNTROWS In Excel Power Pivot
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.
Excel Charts And Logarithmic Scales
Graphing collected data makes the interpretation of the accumulated information much easier to read, comprehend, and share with others.
Free Online Training For Excel
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.
.png "Using Excel’s Multiple Criteria In VLOOKUP Function")
Using Excel’s Multiple Criteria In VLOOKUP Function
Microsoft Excel’s VLOOKUP function is a popular feature amongst office personnel and data processor positions.
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.
INDIRECT Function For Dependent Dropdown Lists In Excel
The INDIRECT function is a powerful function as it allows you to carry out different tasks that come in very handy.
How To Set A Default Template In Excel
Do you need to create new workbooks all the time and then making similar changes to all of them?
How To Disable Scroll Lock In Excel
You are working on Excel and you need to submit work within an hour.
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.
Using RANDBETWEEN For Dates In Microsoft Excel
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.
Use Excel 2016 and Design Your Box and Whisker Chart
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
Microsoft Excel: How To Make Step Chart
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.
Excel 2016 And Its Power Pivot Tool
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?
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.
Using Measures Power Pivot
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.
Top Formulas for Analysts
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 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 Randomize A List In Excel
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.
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.
Remove Spaces in Excel Leading, Trailing, and Double
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.
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 use CONCAT function
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).
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 use SWITCH function in Excel
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).
Excel Practice Exercises And Tests
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.
Spell Check in Excel – Where is it and How to Use it
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.
VLOOKUP for Dummies
VLOOKUP stands for Vertical Lookup. Learning vlookup is very easy but let’s first understand how VLOOKUP works?
8 Excel 2016 New Functions
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.
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 Insert Multiple Rows in Excel
Inserting rows in between selected rows, is one of the most necessary and important functions we require when working in excel worksheets.
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.
Making the characters uppercase and lowercase
Using UPPER(), LOWER() and PROPER() functions with NESTED IF().
.png "Getting power and logarithm of a number")
.png "Getting the row and column number of a cell")
 function.png "Getting real random numbers with RAND() function")
, ISTEXT(), ISNUMBER() and ISBLANK() functions.png "Operation with IF(), ISTEXT(), ISNUMBER() and ISBLANK() functions")
Operation with IF(), ISTEXT(), ISNUMBER() and ISBLANK() functions
Using IF(), ISTEXT(), ISNUMBER() and ISBLANK() functions.
, AND() and OR() formulas.png "Operation with IF(), AND() and OR() formulas")
.png "Operation with two conditions")
Finding minimum, maximum and average number in a list and counting the number of cells.
Using the MIN(), MAX(), COUNT() and AVERAGE() functions on a list
Latest
-
Understanding How HYPERLINK Works In Excel
Excel has a built in function to create hyperlinks &...
-
Understanding how MS Excel Query Works
“Query” in MS Excel has same meaning as ...
-
An Introduction To Excel Power Map
Our today’s post is about “Power Map&rdq...
-
Using Pivot Charts For Displaying Data
Conventional charts are mostly used for displaying r...
-
Using “Calculated Items” For Analyzing A Pivot Table
In our last post, we explored how to use calculated ...
