With near endless cells, it can be hard for the person inputting data to know where to put what data. A data entry form can solve this problem and help guide the user to input the correct data in the correct place. Excel has had VBA user forms for a long time, but they are complicated to set up and not very flexible to change. In this blog post, we’re going to explore 5 easy ways to create a data entry form for Excel....

If you work in a field like carpentry, engineering, architecture, or technology, you probably are! Squaring a number is the mathematical operation of multiplying a number by itself. As an example, a square-sized bedroom measuring 10 feet wide and 10 feet long could be expressed as 100 square feet by multiplying 10 by 10. This post explores all the different ways to square a number in Microsoft Excel. Square a Number with the Multiplication Operator The most straightforward way to square a number is to multiply it by itself using the traditional multiplication operator....

In our example MAX(Range) returns the value 9 since 9 is the maximum value in the range of values {1;0;8;6;9;7;9;6}. MATCH(9,Range,0) looks for a 9 in the Range and will return the position of the first 9 it finds. The 0 in the formula is a predefined Excel parameter that tells the MATCH function to find an exact match in the Range. In our example MATCH(9,{1;0;8;6;9;7;9;6},0) returns the value 5 since the first 9 in {1;0;8;6;9;7;9;6} is in the 5th position....

When it creates the code, elements of it will be hardcoded. For example if you’re importing data from an external source the folder path and file name will both be a hardcoded (static or unchanging) string of text in the M code. If you want to update the folder path or file name to be imported then you need to go into the advanced editor and update the path and file name....

Looking up a value based on one criteria Let’s say we have a table with employees and their total sales and we want a formula to return the total sales based on the employee. Now, if we want to get Allison’s sales, we could use a VLOOKUP formula using this formula. Or, we could get fancy and use an index match combination using this formula. Looking up a value with a row criteria and a column criteria Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West....

To Right Click anywhere in the QAT to quickly access the Customize Quick Access Toolbar menu. In the resulting window there is a Choose commands from drop down menu, you will be able to find all commands listed here by selecting All commands. Excel Options Look for the command called Options from the All commands menu. This command will allow you to quickly open the Excel Options window instead to going to File menu > Options....

Large numbers are often used in an Excel spreadsheet, and this can make them difficult to read if they are not formatted properly. A number in millions or even billions can be difficult to assess and could easily be misread by a factor of 10. Fortunately, Excel provides several easy ways to format numbers with comma separators to help the user with readability. If you have ever viewed financial spreadsheets, the numbers are frequently rounded to the nearest thousand because the numbers are often so large....

Pivot tables are the best way to quickly summarize and analyze large sets of data in Excel. They are easy to use and you can have a summarized report for your dataset in a few quick clicks. There are a lot of entry points for pivot tables, and they are all worth knowing. Get your copy of the example workbook to follow along. Create an Excel Table for Your Dataset The first thing you need to know about pivot tables, is they work best when the source data is inside an Excel Table....

One of the issues in dealing with contact data is the format of their full name. For contacts whose full name appears as First Last, you would likely rather format it as Last, First. Doing so usually leads to easier sorting and filtering in your list. Also, last names are generally more unique than first names which makes any lookups more reliable. This post explores several different ways to reverse the first and last names in Excel....

You’ve probably come across data that looks something like this before. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it. In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount....

Where μ (mu) is the mean and σ (sigma) is the standard deviation. Normal Distribution Probability Density Function in Excel It’s also referred to as a bell curve because this probability distribution function looks like a bell if we graph it. It’s a well known property of the normal distribution that 99.7% of the area under the normal probability density curve falls within 3 standard deviations from the mean. So to graph this function in Excel we’ll need a series of x values covering (μ-3σ,μ+3σ)....

Excel has a lot of ways to group items that are the same, but these rely on values being an exact match. Data might be considered the same even though they are not an exact match. Grouping these similar items can be a difficult and often times manual task. Excel does actually have a feature that can group these close but not quite the same values together! It’s just a bit hidden....

Get The Template Everyone’s got stuff to do and Excel is a great tool to keep track of it all. This is a “To Do List” template to help keep track of all the items you need to get done. This template allows you to assign tasks to various different people, set a priority level and then set the current status of the task to either Not Started, In Progress or Complete....

You might have a mixed set of text and numerical data and need to find out how much of it is text in order to get a better sense of your dataset. In this blog post, you’ll see all the different methods for counting cells containing text data as well as the advantages and disadvantages of each method. Get your copy of the example workbook with the above link and follow along to learn how to count cells that contain text in Microsoft Excel!...

Hidden rows and columns are a common technique in Excel to temporarily remove focus on certain data in your sheet. There are many ways to unhide rows or columns when you want them back, but unhiding the first row or the first column in your sheet can be tricky. Not all methods to unhide will work for row 1 and column A! The usual method to unhide a row or column is to select a set of rows or columns containing the hidden row or column and then use the appropriate Unhide command....

Syntax ADDRESS(Row, Column, Reference Type, Address Type, Sheet Name) Row (required) – This is a number that specifies the row of the address.Column (required) – This is a number that specifies the column of the address.Reference Type (optional) – This is a number (1, 2, 3 or 4) that specifies if the address is a relative or absolute reference.1 – Absolute row and absolute column reference ($A$1 or R[1]C[1]).2 – Absolute row and relative column reference (A$1 or R[1]C1)....

These are great for drawing attention to a single metric like the total sales in your dashboards. In this post we’re going to get extra fancy and add some little additions to the cards like a percentage change over the previous year and a small line chart in the background. To build these fancy KPI cards, we are going to pull out pivot tables, pivot charts, GetPivotData, shapes and custom number formatting from our toolbox of Excel tricks....

Formulas that reference a table are easier to read and write when using the table name instead of a generic range address like A2:A10.Easily add table styles to your data. These styles (and any formats) are automatically applied to new data (rows or columns) added to the table.When you add data to the table formula references automatically update to include this data.Each table has it’s own set of filter and sort toggles....

Enter the comment format editing mode by clicking on the comment and you will see small squares around the comment. In addition to changing the shape, you can also change a variety of other style options. Wow, so fancy!

Syntax ISERROR(Value) Value (required) – This is the value that is checked for an error. Example In this example we test a few values some of which are errors. Only the errors result in TRUE.