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