You’ve probably noticed that columns in Excel do not expand to accommodate the data they contain. When the data in a cell is too wide to fit the column, it just overlaps the next column, or, it’s clipped, when the column to the right contains data. You can fix this problem by setting the column width. Let’s take a look. All columns in Excel have a set width. To check the current width of a column, just click on the column divider to the right....
Let’s take a look. The easiest way to create a custom pivot table style is to first apply a built-in style and then duplicate and customize that style as needed. For example, let’s apply a medium style to this pivot table. You can check that this style is applied by selecting any cell in the pivot table and then checking the PivotTable styles group. The applied style will be highlighted....
But what if your data format keeps changing? Then you either need to change the query to adapt to the new format or change the source data files before they get consumed by power query. Both options aren’t ideal since the whole point of using power query is to automate the whole data transformation process. I recently came across this in my work, in order to get things done I changed the data each time at first....
Is your text data not fully visible or your numerical data appears as #’s? Then here’s a quick way to expand columns in Excel to the exact width needed to view all the data. If the columns you want to select are not adjacent, you can add to your selection by holding ctrl and left clicking the column heading.
The first is that when you enter a 2-digit year, Excel will assign a century to create a valid date using a special rule. When the year is 29 or less, Excel will use the twenty-first century. When the year is 30 or greater, Excel will use the twentieth century. The second thing to be aware of is that Excel does not recognize any dates before Jan 1, 1900. Let’s take a look....
Remember, if you’re not using Excel 2016 or later, then you’ll need to install the power query add-in. Data to Extract In this post we’re going to take a look at how we can pull data from a series of similar pages. I’m a big MMA fan, so the example we’re going to look at is getting a list of all UFC results from Wikipedia. If you visit the Wikipedia page for UFC events there’s a table of Past Events....
However, #N/A errors can also be caused by extra space characters, misspellings, or an incomplete lookup table. The functions mostly commonly affected by the #N/A error are classic lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH. The best way to prevent #N/A errors is to make sure lookup values and lookup tables are correct and complete. If you see an unexpected #N/A error, check the following first: Note: if you get an incorrect result, when you should see a #N/A error, make sure you have exact matching configured correctly....
Voting results source data Many people stop with this basic automatic grouping, but pivot tables can also group data in more sophisticated ways. For example, you can also use a pivot table to group data by numbers – summarizing data by age range, price range, or any numerical range that makes sense for your data. To illustrate, let’s assume you have a list of voting results that includes voter age, and you want to summarize the results by age group....
And if you don’t like these numbers/texts are too close to the cell border, you also have an option to indent the content of the cell. You can indent to the left or to the right in a cell. In this short Excel tutorial, I will show you how to indent In Excel. There is a button to do that right there in the ribbon, and you can also use a keyboard shortcut (if you are more comfortable with that)....
In this worksheet, we have the first 10 letters in the alphabet in the range B5:B14. How can we sort this data in random order? One way to do this is to add a helper column and use RAND function to generate random values. Then we can use the SORT function to sort data by the helper column. This works, but it clutters up the worksheet. This is a situation where it makes sense to use the SORTBY function, which can use arrays that don’t exist in the source data to sort....
And one such common thing people often have to do is to remove the first characters from a string in Excel. Suppose you have a dataset as shown below (in column A) and you want to remove only the first characters from each cell and keep the rest of the text as is (resulting data shown in column B). There are multiple ways to do this in Excel… And in this tutorial, I will show you different ways to remove the first characters from a string in Excel....
One of the nice things about slicers, compared to report filters, is that they can control multiple pivot tables or charts. This makes slicers very useful for building dashboards that are based on pivot tables. In this worksheet, we have a simple pivot table that shows total sales by Product. I’m going to duplicate the pivot table, and set the second pivot table to show sales by City. Next, I’ll add a slicer to filter by Region....
Let’s take a look. In this example, we have a table of commission rates on the right and a list of sales numbers in a table on the left. I’ve already created a named range for the Sales numbers in column F. Let’s use MATCH to look up the correct commission tier for each sales number. I’ll start off entering the MATCH formula in C6. The match value comes from column B, and the lookup_array is “sales”....
The result is $17.00, the Price of a Large Red T-shirt. This is an array formula and must be entered with with Control + Shift + Enter in Legacy Excel. Note: In the current version of Excel, you can use the same approach with the XLOOKUP function. Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria....
In the example, we need to map the numbers 1-6 as follows: Since there is no way to derive the output (i.e. it’s arbitrary), we need to do some kind of lookup. The VLOOKUP function provides an easy way to do this. In the example shown, the formula in F7 is: Although in this case we are mapping numeric inputs to numeric outputs, the same formula will handle text values for both inputs and outputs....
As the formula is copied down, it calculates a 3-day moving average based on the sales value for the current day and the two previous days. Below is a more flexible option based on the OFFSET function which handles variable periods. About moving averages A moving average (also called a rolling average) is an average based on subsets of data at given intervals. Calculating an average at specific intervals smooths out the data by reducing the impact of random fluctuations....
Inside the REPT function, the text to repeat is provided as a single asterisk ("*"). The number of asterisks needed for each value is determined with with the LEN function in this bit of code here: We start with 12, then subtract the length of the text in column B. In cell B5, “Sebastian” is 9 characters, so the result is 3. The formula is evaluated like this: The results in column C is formatted with a monospaced font (Courier New) to clearly show all strings are the same length....
Fields The pivot table shown is based on two fields: Name and Color. The Color field is configured as a row field, and the name field is a value field, as seen below: The Name field is configured to summarize by count: You are free to rename “Count of Name” as you like. Steps Notes Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Note: When there are ties in top or bottom values, Excel will display all tied records. In the example shown, the pivot table is filtered on top 3, but displays 4 players, because Borg and Djokovic are tied for third place. Data The source data contains three fields: Year, Country, and Champion. This data is contained in an Excel Table starting in cell B4. Excel Tables are dynamic and will automatically expand and contract as values are added or removed....
One thing to note is the approval and disapproval ratings don’t add up to 100%. This is due to some survey respondents expressing no opinion. I played with plotting “no opinion” as a another data series in the chart, but decided to leave it out to keep things simple. Update: The Washington Post now has a full page of fancy poll result charts. Steps to create Data Sources: langerresearch.com, Washington Post-ABC Poll (Reagan-Trump), Gallup polls (Eisenhower-Carter) | wapo....
