For example, you may want to get a consistent look in your data set as shown below: In this tutorial, you’ll learn various ways to add leading zeroes in Excel (i.e., add a 0 in front of a number): Converting the Format to Text Using Custom Number Formatting Using Text Function Using REPT/LEN functions Using VBA Each of these methods has some merits and drawbacks (covered in each section). Let’s see how each of these works....
Here we have data for monthly sales of a small business for the year 2016. Let’s build a column chart to plot this data. To start off, put the cursor anywhere in the data, then visit the Insert tab on the ribbon. Since I want a column chart, I’ll use the bar icon to the right of Recommended Charts, which contains both column and bar chart options. Now let’s make this a better chart....
Let’s take a look. Normally, when you choose a chart type on the ribbon, Excel creates an embedded chart which floats over the worksheet grid below. But if you’re short on space, or, if you’d just rather have a chart in its own worksheet, you can easily do so. To move a chart into its own sheet, first, select the chart. Then, go to the Design tab under Chart Tools and click the Move Chart button....
Let’s take a look. By default, a new pivot table will summarize all of the source data. However, the report filter gives you a way to quickly limit the information that appears in the table by filtering on one or more fields. For example, this pivot table shows Total Sales by product. If we add the City field as a Column Label, we see a breakdown of sales by city in each column....
Preventing #REF errors The best way to prevent #REF! errors is to prevent then from occurring in the first place. Before you delete columns, rows, or sheets be sure they aren’t referenced by formulas in the workbook. If you are copying and pasting a formula to a new location, you may want to convert some cell references to an absolute reference to prevent changes during the copy operation. If you cause a #REF!...
You must have seen it on hundreds of web pages where you are asked to select an option by clicking on a small round shape next to the text. As soon as you select it, it gets a black dot in it (an indication that is marked). The same thing can also be created in Excel. In this tutorial, I will refer to it as the Radio Button (as my Computer teacher taught me)....
A worksheet is an area that has all the cells where you can store data, enter formulas, insert charts and create reports and dashboards. When you open a new Excel workbook file, by default there is only one worksheet. Earlier versions of Excel (2013 or 2016 used to have 3 worksheets by default) There are some simple shortcuts and techniques that you can use to quickly insert new worksheets in the same workbook in Excel....
Let’s take a look. Here we have a workbook that contains several sheets. Let’s rearrange these worksheets into alphabetical order. To move a worksheet, just click on the tab with your mouse and drag the sheet to a new location. Excel will display a small black triangle to let you know exactly where the new sheet will be placed. To complete the move, just release the mouse. The worksheet tab will appear at the new location....
Today I was using power query to import external data in a CSV file and then do some transformations on the data. I decided I wanted to import my data as a connection only first then perform the transformations in a separate query. I had My Sales Data connection only query ready to use in another query. I hadn’t really noticed before, but there’s actually no command in the ribbon to get data from a query....
But in some cases, instead of getting the value, you may want the formula to return the cell address of the value. This could be especially useful if you have a large data set and you want to find out the exact position of the lookup formula result. There are some functions in Excel that designed to do exactly this. In this tutorial, I will show you how you can find and return the cell address instead of the value in Excel using simple formulas....
And sometimes, it could be a factor outside of Excel – such as a power failure or your system hanging because of an issue other than Excel. In such cases, to make sure you don’t lose your data, Excel has an AutoSave feature – which as the name suggests, will automatically save your work. With autosave enabled, Excel will save your work at regular intervals, which will make sure you don’t lose a lot of your work (at max a few minutes of work can be lost)....
Let’s take a look. Here we have a small table of data that represents widget sales over a 3-month period. We have the quantity sold of each widget already in the table. We just need to add Sales, which equals the quantity sold x the price. The price appears in column C. For January, we just need a simple formula. The formula for Widget A is D9 * C9. And if we copy this formula down, we get the correct result for each widget type....
Let’s take a look. Like our previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s build the formulas we need using division. The first formula doesn’t use any cell references. We just input = 10 / 2, which gives a result of five. The second example is D6 divided by 7, so we need to use both a number and a cell reference....
When to use Excel MIN Function MIN function can be used when you want to get the smallest value from a set of values. What it Returns It returns the smallest value from a set of values. Syntax =MIN(number1, [number2], …) Input Arguments number1 – the first number from a set of values from which you want to get the minimum value. [number2], .. – (optional) second number from a set of values from which you want to get the minimum value....
Let’s take a look. Here we have a list of employees in a table. Let’s use VLOOKUP to build a simple form that retrieves the information for a given employee based on their ID number. The first thing I’ll do is create a named range for the data in the table, and a named range for the ID that we’ll be using to look things up. By using named ranges, our lookup formulas will be easier to understand and easier to copy....
This page describes just one approach, where you make a line chart with two data series (one high, one low) and then use “up/down bars” to create the floating columns. The advantage of this approach is simplicity. There is no need to add calculations and a ghost data series to float the columns, as you’ll see in other examples. The data used to make the chart on this page looks like this:...
Data Below is a small sample of the data used to plot this chart. This data can be downloaded from Yahoo Finance. Steps to create Create a Line Chart with Date and Close data only. Remove extra data series, leaving only Date and Close. Make sure x-axis is formatted as a Date axis. Set Major units to 5 years, minor units to 1 year. Set bounds to 1/1/1990 and 1/1/2020. Apply number format to the axis (“yyyy”) in this case....
One of the things where Power Query can save you a lot of time is when you have to merge tables with different sizes and columns based on a matching column. Below is a video where I show exactly how to merge tables in Excel using Power Query. In case you prefer reading the text over watching a video, below are the written instructions. Suppose you have a table as shown below:...
This formula will work to calculate next upcoming birthday as well. Note: in this case, we are arbitrarily fixing the as of date as June 1, 2017 in all examples. Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months. Next, the month value goes into the EDATE function, with the original date from column B....
Source data The source data contains three fields: Date, Sales, and Color converted to an Excel Table. Below are the first 10 rows of data: Fields The pivot table uses just two of the three fields in the source data: Date, and Sales. Because Date is grouped by Years and Months, it appears twice in the list, once as “Date” (month grouping), once as “Years”: The Date field has been been grouped by Months and Years:...
