Excel comes with many built-in PivotTable Styles, in Light, Medium and Dark colour themes. You can quickly apply any of those styles to the selected pivot table – just open the style palette, and click on the style that you want to apply.

You can’t change those built-in styles, but you can create your own pivot table custom style, based on a built-in style. Then, modify your custom style, with the formatting that you want.

This video shows how to create a custom style from an existing style, and make changes to its formatting.

If you’re using Excel 2013 or earlier, this short video shows how to copy one of your fancy custom styles into a different workbook.

There are written instructions on my website, if you’d rather read than watch.

**NOTE**: If you’re using Excel 2016, see the instructions in the next section.

Thanks to Annie Cushing from Annielytics.com, who let me know that the old method to copy a pivot table custom style wasn’t working in Excel 2016.

With a bit of experimenting, I found a new way to copy the custom style. It’s even easier than the old way, and it worked in Excel 2016 and Excel 2013.

- Open the workbook (A) with the pivot table that has the custom style applied.
- Open the workbook (B) where you want to add that custom style
- Position the workbooks, so you can see the sheet tabs in both files
- Press the Ctrl key, and drag a copy of the pivot table sheet from workbook A, into the workbook B.
- The custom style is now copied into the new workbook, and you can see it in the PivotTable Style palette.
- In workbook B, you can delete the sheet that you copied from workbook A.

For more pivot table formatting tips, go to the Excel Pivot Table Format page on my Contextures site.

There are written instructions and videos, that show how to create and copy PivotTable Styles, keep pivot table formatting, and other tips.

___________________________

____________________________

]]>

The quickest way to compare two cells is with a formula that uses the equal sign.

**=A2=B2**

If the cell contents are the same, the result is TRUE. (Upper and lower case versions of the same letter are treated as equal).

If you just want to compare two cells, but aren’t concerned about leading spaces, trailing spaces, or extra spaces, use the TRIM function to remove them, for one or both of the cells.

**=TRIM(A2)=TRIM(B2)**

That can help if you’re trying to match text strings to the values in an imported list, such as this VLOOKUP example.

If you need to compare two cells for contents and upper/lower case, use the EXACT function. As its name indicate, that function can check for an exact match between text strings, including upper and lower case. It doesn’t test the formatting though, so it won’t detect if one cell has some or all of the characters in bold, and the other cell doesn’t.

**=EXACT(A2,B2)**

See more EXACT function examples in my 30 Excel Functions series.

Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters, or a 3-digit code at the end of a string.

To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:

**=LEFT(A2,3)=LEFT(B2,3)**

To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:

- =
**RIGHT(A2,3)=RIGHT(B2,3)**

You can combine LEFT or RIGHT with TRIM, if you’re not concerned about the space characters:

**=RIGHT(TRIM(A2),3)=RIGHT(TRIM(B2),3)**

And combine LEFT or RIGHT with EXACT, to check if upper/lower case match too. This formula will ignore extra spaces, but checks the case:

**=EXACT(RIGHT(TRIM(A2),3),RIGHT(TRIM(B2),3))**

Finally, here’s a formula from UniMord, who needs to know how much of a match there is between two cells. Are the first 5 characters the same? The first 10? What percent of the string in A2, starting from the left, is matched in cell B2?

Here’s a sample list, where the addresses in column A and B and being compared.

The first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:

**=LEN(A2)**

The formula in column D is doing the hard work. It finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared.

**=SUMPRODUCT(**

–(LEFT(A3,

ROW(INDIRECT(“A1:A” & C3)))

=LEFT(B3,

ROW(INDIRECT(“A1:A” &C3)))))

The INDIRECT function creates a reference to a range of cells, starting from cell A1. The range ends in column A, in the row that matches the length calculated in column C. So, in row 2, that range is A1:A9.

The ROW function returns the **row for each of the rows** in that range. That’s why we use ROW/INDIRECT, instead of just referring to the length in cell C2.

In this screen shot, I’ve used the F9 key to calculate that part of the formula, and you can see the row numbers.

Then, the LEFT functions return the characters that are 1, 2, 3…characters to the left in each cell. In this screen shot, I’ve calculated both of the LEFT functions, and you can see that there is a match for lengths 1 through 9.

However, if I do the same thing in row 5, only the first character is a match. After that, the characters are different in the two cells.

The equal sign compares the values for characters 1 through 5 in this example, and returns TRUE if they match, and FALSE if they do not match.

The double minus sign converts each TRUE to a 1, and each FALSE to a zero.

Finally, the SUMPRODUCT function adds up those numbers, to give the number of characters, from the left, that match. In row 5, that total is 1

Once the length and match length have been calculated, it’s easy to find the percent matched. This formula is in cell E2, to compare the lengths:

**=D2/C2**

There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.

Thanks, UniMord, for sharing your formula to compare two cells, character by character.

Here are a few more articles that show examples of how to compare two cells – either the full content, or partial content.

- Use INDEX, MATCH and COUNTIF to find codes within text strings. There are other formulas in the comments too, so check those out.
- Compare formulas on different sheets, with the FORMULATEXT and INDIRECT functions. Those functions are volatile though, so they’d slow down the workbook if you use too many of them.
- Be careful when using the Remove Duplicates feature in Excel – it treats real numbers and text numbers as the same value

__________________________

___________________

]]>This solution should help you spot hidden rows and columns, but don’t **depend solely on this when you’re doing critical work**.

Use visual checks for filtered or hidden rows, or hidden columns, and remember to look for hidden sheets too.

As always, our goal is to make things **idiot resistant**, not idiot proof.

Here’s how I set up the Excel hidden data warnings.

At the top of the sheet, in cell B2, there’s a formula to check for hidden rows. It counts the missing rows, whether they’re hidden manually, or by a filter.

In this screen shot, no filters have been applied to the table, but rows 12:13 were manually hidden, so the formula result is 2.

Here’s the formula that’s in cell B2:

**=COUNT(Table1[OrderCount]) – AGGREGATE(2,5,Table1[OrderCount])**

First, the formula counts all the numbers in the OrderCount column. That column has a simple formula that returns a 1 in each row.

**=1**

Counting those cells should give an accurate count of the number of rows in the table.

**=COUNT(Table1[OrderCount]) **

Next, the AGGREGATE function counts the visible cells in the OrderCount column.

**AGGREGATE(2,5,Table1[OrderCount])**

AGGREGATE uses function type 2 ( COUNT), and is set to ignore hidden rows (option 5).

In this example, there are 21 rows in the table (COUNT) and 19 visible rows (AGGREGATE)

To find the number of hidden rows, subtract the visible rows from the total count, and the result is 2 hidden rows.

Unfortunately, AGGREGATE doesn’t work for columns, just rows, so how can you tell if columns are hidden?

A hidden column would have zero width, so I used the CELL function to check the cell widths in the top row, cells A1:J1.

**=IF(CELL(“width”,A1)>1,1,0)**

Then, in cell B3, a formula subtracts the sum of those cells, from the count of the cells.

**=COUNT(A1:J1)-SUM(A1:J1)**

In theory, that solution works, but the results didn’t automatically update if columns were hidden or unhidden. In this screen shot

- D and E have been unhidden, but are still showing zeros
- F, G and H are hidden, but are still calculating as 1
- The Hidden Columns total shows 2, instead of 3

To see the correct number of hidden columns, you can press F9 to recalculate.

An Excel hidden data warning isn’t too helpful, if you have to remember to recalculate.

But, as a conditional formatting rule in cells A1:J1, it seems to work nicely. I’ve only tested in a small file though, so **your results might be different**.

With cells A1:J1 selected, I created a new formatting rule, using this formula:

**=CELL(“width”,B1)=0**

A cell turns yellow, if the cell to its right is hidden (0 width).

In this screen shot, columns D:E and H:I are hidden. As a result, cells C1 and G1 have yellow fill colour, based on the conditional formatting rule.

Charles Williams found that conditional formats “are not executed at a calculation unless they are **on the visible portion of the screen**“.

So, if you try this Excel hidden data warning technique:

- Be sure to lock the top row.
- Recalculate too, just to be sure that the correct cells are coloured.
- Before you do any critical data analysis, do a visual check to see if any rows or columns or entire sheets are hidden.

Remember, as the old saying goes, it’s better to be safe, than to read about your catastrophic errors on the internet.

To download the Excel hidden data warning workbook, go to the Conditional Formatting Examples page on my Contextures site. In the Download section, click on the link for the Hidden Data Warning sample.

The zipped file is in xlsx format, and does not contain any macros.

________________

________________________

It’s Easter this Sunday, but if you’d like to verify that, or find out when Easter will be next year, use one of the Easter date calculations that Jerry Latham shared here, a few years ago.

- There are several worksheet formulas, and notes on their limitations.
- There are also four User Defined Functions, that use different methods for calculating the Easter date.

In the screen shot below, the following formula is in cell B4:

**=DOLLAR((“4/”&B2)/7+MOD(19*MOD(B2,19)-7,30)*14%,)*7-6**

You can download a workbook the code and formulas, from my Contextures website.

As I mentioned earlier, I recently had my Contextures site and this blog moved to a different server. I hope that it is faster, and has minimal down time.

The only casualty that I’ve found so far is the comment section on this blog. Unfortunately, comments that were posted during the loooong transition time (March 14th to March 25th), were lost in the shuffle.

Other than that, things seem to be okay, but please let me know if you notice anything missing, or broken.

One of the key steps for moving to a new servers was to create DNS records for the them.

The form had boxes for the IPv4 address and the IPv6 address. Uh-oh!

Fortunately, I found examples of the different formats.

**IPv4**: 192.168.99.1**IPv6**: 0:0:0:0:0:ffff:c0a8:6301

To get that IPv6 address, I used an online converter.

Looking at those two IP addresses, I couldn’t see how one was converted to the other. More Googling took me to this page that explains the conversion.

- Each chunk of the IPv4 address is converted from decimal to hex, to get the IPv6 version.
- The hex version each converted chunk becomes 2 characters
- Those 8 characters are at the end of the IPv6 address.

When I used the online conversion tool, all the IPv6 addresses started with the same string:

**0:0:0:0:0:ffff:**

According to Wikipedia, that is the prefix used when PIv4 is mapped to an IPv6 address

Fortunately, my registrar said that the IPv6 version wasn’t required, so I just entered my IPv4 addresses.

But even though I didn’t need one, why not build an IPv4 to IPv6 converter in Excel? It can convert Decimal to Hex, and it’s better than an online tool! Am I right?

Here’s a screen shot of the first few columns on the conversion sheet.

**NOTE**: This project was just for fun, and might not be accurate for what you need. If you need an IPv4 address converted to IPv6 format, che**ck with your registrar or your hosting company**.

There are 3 sets of formulas for the conversion, and a final formula to pull the pieces together.

These formulas locate the 3 dots in the IPv4 address

- =FIND(“.”,[@IPv4])
- =FIND(“.”,[@IPv4],[@Dot01]+1)
- =FIND(“.”,[@IPv4],[@Dot02]+1)

These formulas to pull out the decimal numbers, between the dots

- =–LEFT([@IPv4],[@Dot01]-1)
- =–MID([@IPv4],[@Dot01]+1,[@Dot02]-[@Dot01]-1)
- =–MID([@IPv4],[@Dot02]+1,[@Dot03]-[@Dot02]-1)
- =–REPLACE([@IPv4],1,[@Dot03],””)

These formulas convert each decimal number to hex

- =TEXT(DEC2HEX([@IP01]),”00″)
- =TEXT(DEC2HEX([@IP02]),”00″)
- =TEXT(DEC2HEX([@IP03]),”00″)
- =TEXT(DEC2HEX([@IP04]),”00″)

The formula in column B combines all the pieces, and starts with the mapping prefix (MapPre). The result is changed to lower case

- =LOWER(MapPre &[Hex01]&[Hex02]&”:”&[Hex03]&[Hex04])

Then, because why not, I made another set of formulas to convert IPv6 addresses to IPv4.

This was easier, because each chunk is equal length – there’s no need to find the location of each chunk.

These formulas use the HEX2DEC function, to convert the 2-digit hex codes to decimal numbers. You can download the sample file, to see the formulas.

To see all the formulas for the IP address conversion, go to the Excel Sample Files page on my Contextures site.

In the Functions section, look for **FN0054 – Convert IP Addresses IPv4 to IPv6**. The zipped file is in xlsx format, and does not contain any macros.

____________________

______________________

]]>

**Best Lists **– Here’s a round up of the best data visualization lists from last year, compiled by data journalist, Maarten Lambrechts. This should inspire you for your 2018 projects.

**Beginners **– On the Flowing Data blog, Nathan Yau posted a link to Beginners Guide to Visualization literacy. Click that link, then click the “static version” link.

**Dashboards **– Audry Loper shows how she changed boring 6-page reports into informative 2-page reports. Her tips are useful for Excel dashboards too. And if you want to make those dumbbell charts, Mynda Treacy has instructions.

**Charts** – Jon Peltier shows how to work with blank cells, or #N/A cells in Excel charts. There are details for older versions of Excel, and the new setting in Excel 2016.

**Top Tips** – Listen to John Michaloudis’ podcast, to hear the best Excel tips from 2017. My pivot table tip is #8, at the 14:00 mark. The podcast player is at the top of the article, and you can download to listen offline.

**Excel Tips **– Neil Patel shares 7 advanced Excel tips. The examples show how to analyze website data, but are useful for other types of Excel projects too. NOTE: Tip #4 is a function for Google Sheets, not Excel.

**Comments** – On the Excel Esquire blog, Ben J. Kusmin shows how to find and review comments in Excel files. There are comment macros on my site, and comment tools in my Excel Tools add-in.

**Data Entry **– Jan Karel Pieterse shows how to add an Info button for data entry cells. He uses Excel Styles and hyperlinks, but you could keep it simple and skip those steps. Add them later, for a fancier setup.

**Excel Ribbon** – Doug Glancy is sharing his free tool to create and edit the code for custom tabs in Excel Ribbons. Doug couldn’t use the Custom UI Editor that I showed, so he built his own add-in that runs within Excel (2010 and later versions).

**Security** – A recent security update has given admins the ability to disable Excel’s Dynamic Data Exchange (DDE) prompts. Woody Leonhard explains the changes, and where you can find the details – they’re in an old security bulletin!

**Odd Facts** – On the Data Rails blog, they posted “6 Cool Facts About Excel“, but I can’t find any proof of the 2nd (names) or 3rd (toolbars) items. Mike Alexander also mentioned those facts in an old post, and there’s some discussion in the comments, but no links to a source.

**Early Days **– If you remember the early days of personal computing, you might find this story as funny as I did. Lots of executives suddenly had to do their own computer work, and it was a struggle!

**Planning** – Nick Orso has an interesting use for Excel – planning the layout of a mobile cabin. Skateboards are involved too!

**Photography – **For an entertaining use of spreadsheets, watch this video on Excel and HDR photography.

**UK Excel** – Would you be interested in an Excel related **conference in the UK** this year? If you are, please leave a comment on Simon Murphy’s blog post.

**Unlock Excel**: CPA Australia is hosting another round of Unlock Excel conferences in **April 2018**. Events are in the following cities: Melbourne: April 9-10, Sydney: April 12-13, Brisbane: April 16-17. Get all the details on the Unlock Excel website.

**Amsterdam Summit**: Registration is open for this year’s Amsterdam Excel Summit – **June 7-8, 2018**. The Summit day (June 7) will have a keynote address by a Microsoft Program Manager, and 8 sessions split over 2 tracks. On Masterclass day (June 8), there are two options – Excel Business Modeling and Power Query.

**Excelapalooza**: This Excel-based user conference is in its 7th year, and runs **Sept. 17-19, 2018, **in Dallas, Texas. Get more details on the Excelapalooza website.

**Problem Solving** – Mike Girvin (ExcelIsFun) has an interesting new “Excel and Business Math” series on YouTube. This video shows the 5 key steps for solving math problems in Excel.

**Statistics** – If you’d like to learn more about statistics, Crash Course has a great intro series on YouTube. It might help you get ready to use Excel’s statistical functions.

**Power Query** – Learn more about Power Query, from experts Ken Puls and Miguel Escobar. Get the free trial of their online Power Query Academy, and watch 7 videos from their full course (about 1 hour).

_______________________

]]>

Someone asked if it was possible to use conditional formatting in an Excel pie chart. They had found formulas to add conditional formatting in a column chart, but that didn’t do what they needed.

In this case, the data had two different amount – the total orders, and the total amount that had been invoiced. From those amounts, the Percent Invoiced had been calculated. The pie chart needed to show

- slice size based on the order amount
- slice colour based on a colour scale for the rank for the % invoiced

That sounded like an interesting challenge, so I decided to tackle it.

- To investigate the problem, I created a small dataset, similar to what the email had described. The first column had client names
- Second column showed total order amount
- Third column had the total amount invoiced
- Final column calculated the percent invoiced
- There was color scale (Green-Yellow-Red) on the % Invoiced column

Here’s a screen shot of my sample. It has 11 Client names – the same number that the email question had used.

- The highest % Invoiced cell is for Ann – it’s dark green, because that’s a good value.
- The lowest % Invoiced is for Hal, and it’s dark red, because that’s a bad thing!

Here’s what the completed chart should look like.

- Ann and Bob have large slices, and they’re both coloured green, so their invoicing percentage is good.
- Hal and Ida have small slices, but they’re coloured red, so the invoicing has fallen behind for those clients.
- Fran has one of the largest slices, and it’s coloured orange, so that account should be checked soon!

But, how can we get those colors into the chart?

The sample data has 11 clients listed, so the chart needed to match a color scale of that size.

- First, I added a ranking column in the sample data, to calculate the rank for each % Invoiced amount.
- Next, I added a Red – Yellow – Green color scale formatting to the Rank column cells.

As you can see, the colors are slightly different, because the percentages aren’t equally distributed, like the numbers 1 to 11 are.

So, I removed the conditional formatting from the % Invoiced column, and decided to use the colours for 1 to 11.

Next, I created a Color List

- On a “Colours” sheet in the workbook, I typed a list of numbers, from 1 to 11.
- Then, I applied a Red – Yellow – Green conditional formatting color scale to those cells.
- That list was named, as ColorList
- Next, I needed to find out what those colors were, so I could use then in the chart.

And that’s when the fun began.

If you manually check the color for one of those cells, it shows “No Fill”, because the color comes from conditional formatting.

A Google search found lots of macros that returned the fill color, but only a few that promised to return a conditional formatting color. And none of those worked for me.

- Chip Pearson has code for conditional formatting colors, but it doesn’t seem to work in newer versions of Excel
- Rick Rothstein has a macro to get the conditional formatting colour (comment #5), but that didn’t work with my colour scale

Since I couldn’t get the conditional formatting color with a macro, I had to resort to brute force, to get the color for each ranking number.

Close your eyes, if you’re a squeamish type – this gets a bit ugly!

- To get the colors, I copied the ColorList cells.
- Then, I pasted them into Word
- Next, I copied the list from Word, and pasted them back onto the 1-11 cells in Excel
- The conditional formatting was gone, and the cells had a fill color instead.

The colors were the same, but now Excel could extract the color number from those cells

In the sample file, I also created a Bar Chart, because it’s easier to compare the client amount in that type of chart, instead of a pie chart.

Here’s the macro – ColorChartDataPoints – that I use to color the pie chart slices, and the bars in the Bar Chart, based on the % Invoiced rank for each client.

- The macro gets to location of the source data, based on the formula for the chart series.
- Then, it gets the Rank number from the 4th column to the right of the source data’s start column
- Based on that Rank number, the matching cell’s fill color from the Color List is used for the slice/bar.

Sub ColorChartDataPoints() 'colour data point based on 'value in rank column Dim ws As Worksheet Dim wsC As Worksheet Dim ch As ChartObject Dim ser As Series Dim dp As Point Dim ptnum As Long Dim rngColor As Range Dim rngSD01 As Range Dim strF As String Dim strRng As String Dim CharStart As Long Dim CharEnd As Long Dim ColOff As Long Dim PtRank As Long Dim PtColor As Long ColOff = 4 'offset to Rank column Set ws = ActiveSheet Set wsC = Sheets("Colours") Set rngColor = wsC.Range("ColorList") For Each ch In ws.ChartObjects Set ser = ch.Chart.FullSeriesCollection(1) strF = ch.Chart.SeriesCollection(1).Formula CharStart = InStr(1, strF, ",") CharEnd = InStr(InStr(1, strF, ",") _ + 1, strF, ",") strRng = Mid(strF, CharStart + 1, _ CharEnd - CharStart - 1) Set rngSD01 = ws.Range(strRng) ptnum = 1 For Each dp In ser.Points PtRank = rngSD01.Cells(ptnum, 1) _ .Offset(0, ColOff).Value PtColor = rngColor.Cells(PtRank, 1) _ .Interior.Color dp.Format.Fill.ForeColor.RGB = PtColor ptnum = ptnum + 1 Next dp Next ch End Sub

Here’s the finished report, with the sample data, the bar chart, and the pie chart. It was an interesting project, so download the workbook (link below), if you’d like to test it.

NOTE: There is also an event procedure on the Chart sheet, and it automatically runs the ColorChartDataPoints macro, if an Order total or Invoice amount is changed.

All this work with Excel colors reminded me that John Marshall had sent me his Excel Colours project a while ago. If you’d like an Excel file with tons of colour lists, you can download a copy of John’s sample file from the Technet Gallery.

John is a Microsoft Visio MVP, and needed colour lists for a Visio project. That’s easier to do in Excel, so he set up the colour lists there. The workbook has 11 sheets with colour samples, and the RGB, Hex, and other values. The main list is shown below

If you select a cell, and go into the More Colors option, you can see the RGB settings for the selected cell, on the Custom tab. Those numbers should match the values that you’ll see in the Colour lists of John’s workbook.

There are several other colour lists in John’s workbook too, including Crayola, LEGO and Pantone. On the workbook’s Introduction sheet, John gives details on the source for each list.

To keep the workbook from slowing down, the colour list formulas are in the first row only (orange cells). Below that, the formulas have been copied, and pasted as values (green cells). If you change any of the colours, you could copy the formulas down again.

Most of the formulas are based on User Defined Functions (UDFs) that calculate each colour’s values.

In the screen shot shown below, you can see a formula that uses a UDF named RBG_to_HSV UDF. It calculates the colour’s Hue, based on the RGB (Red Green Blue) values in columns B, C and D.

The workbook and its code are unlocked, so go to the Visual Basic Editor, and take a look at the functions in the code module, to see how they work.

Here are a few more of the Excel Colour, and Excel Chart Colour resources that I used, while working on this interesting project.

Color Info

- StackOverflow – Get Cell Color Properties
- Jon Peltier explains pretty much everything you’d want to know about Excel colors in the user interface
- My blog post shows how to make the Colors window bigger
- Wikipedia – The RGB Color Model and the HSL/HSV Values

Chart Colors

- Jon Peltier’s macro to change series color based on cell color
- Jon Peltier’s macro to change series color based on the series name

Here’s how to get the two sample Excel files that I mentioned:

- To get a copy of my sample file, go to the Excel Sample Files page on my Contextures website. In the Charts section, look for CH0011 – Chart Colour Based on Rank. The zipped file is in xlsm format, and contains macros.
- You can download a copy of John Marshall’s Colour Lists workbook from the Technet Gallery.

________________________________

]]>

This seating plan with charts is a new version of one that I posted long ago (2006), and the new version is easier to use.

You can read details about the old version, including how it was set up. That version used drop down lists, based on a formula that hid the guest names after they were assigned to a table.

The tables and chairs were circle shapes, and each chair was linked to a cell. That worked well, if you didn’t have too many guests, but was a lengthy process to add more tables and seats for a large party.

I just created a new version, which is easier to use. Instead of circle shapes, this plan uses doughnut charts – thanks to Oz du Soleil for that idea.

The main sheet in the workbook is named TablePlan. On that sheet, you assign guests to seats at the different tables.

For each table, the guest names and table number cells are the source data for one of the doughnut charts. The chart for Table 1 is shown below, with its source data highlighted.

Here’s what I’ll cover in this blog post:

- First, I’ll show you how to use the sample file, with the fake guest list.
- Later, you’ll see how to add more guests or tables to the Excel seating plan with charts, or change the number of seats per table.
- After that, if you’re interested in the setup details, I’ll explain the formulas and other features in the sample file.
- Finally, there is a link where you can download the Excel seating plan with charts sample file.

Here are the basics on how to use the sample file, with its fake guest list. The sample file has 24 guests, who can be seated at 3 tables, which have 8 seats per table.

These instructions show how to assign those fake guests to the 3 tables. Later you’ll see how to create your own guest list, and assign them to tables.

In the Guest column, the cells have drop down lists. To assign a guest to a seat:

- Select the cell at the table and seat where the guest will sit
- Click the drop down arrow, to see the guest list
- Scroll down to find the guest name
- Click on the guest name to select it.

NOTE: Choose names that at the top of the list, above the “END OF LIST” item.

The selected guest’s name appears in the chart for that table.

In the sample file, there is sheet named Lists. In column B on that sheet, there are 24 guest names. After the last guest name, there is a final entry – END OF LIST.

To create your own guest list:

- Clear all the names from column B on the Lists sheet – leave the END OF LIST entry at the bottom of the list
- If you have fewer than 24 guests, delete the extra rows, from the middle of the list
- If you have more than 24 guests, insert more worksheet rows, somewhere in the middle of the list. This will affect the list in columns H:I too – that’s okay.
- Then, type the names of your guests, in column B
- Be sure that END OF LIST is in the cell below the last guest name
- Then, copy down the formulas, in columns C, D and E, to the bottom of the guest list (including the END OF LIST row)

Then, update the list in columns H and I:

- in column H, renumber the list, so this a number for each guest.
- In column I, fill the formulas down to the bottom of the list.

If you need more tables in the seating plan, follow these steps:

- On the TablePlan sheet, copy one of the existing table lists, such as B10:D17
- Paste the copied cells below the last table list
- Change the table number in the pasted list, and clear any Guest names

Here is the new list for Table 4.

Next, follow these steps to create the chart for the new table:

- Copy one of the existing table charts, and paste it onto an empty area on the sheet
- Change the table number in the chart title
- With the new chart selected, use one of these methods to change the data source:
- Point to the border of the highlighted data source, and drag it down to the new table’s data.
- OR

The sample file is set up with tables that have 8 chairs each. If your tables have more seats, follow these steps to add more chairs:

- insert a couple of rows, to add extra chairs at one or more of the tables. The doughnut chart will adjust automatically, to show the extra chairs.
- Add the table number in column C
- Renumber the seats, from 1 to 10, in column D

In this screen shot, I’ve inserted 2 rows below Ian’s name at Table 1. The doughnut chart shows 10 seats now, with 2 empty seats.

NOTE: The doughnut chart slices were manually changed to pink and grey, so the new slices could be coloured too.

In the next few sections, there are details on how the Excel seating plan with charts works. You can skip down to the Download section, if you don’t need to know the “under the hood” stuff.

This version of the seating plan keeps all the guest names in the drop down list, instead of removing them. That prevents data validation error warnings from appearing on the sheet.

In the drop down list (shown above),

- Guests who have NOT been assigned to a seat appear first
- After those names, there is an “END OF LIST” item
- Below that, you’ll see the names of guests who are already assigned to a seat

There are formulas in columns C, D, E and F on the Lists sheet, beside the list of Guest names.

If you add more rows for guest names, be sure to copy those formulas down to the last row of guest names.

In column C, the formula returns the table each guest has been assigned to, if any. Or, if a guest has accidentally been assigned to more than one table, the result is “Multi”.

**=IF(COUNTIF(TablePlan!B:B,B2)>1, “Multi”, IFERROR(INDEX(TablePlan!$C$2:$C$25, MATCH(B2, TablePlan!$B$2:$B$25,0)),”—”))**

**NOTE**: If you add more tables to the TablePlan sheet, change the cell references (in red) in the INDEX and MATCH functions, to include all the data entry rows.

In the screen shot below, Ken has been assigned to multiple tables, so that needs to be fixed on the TablePlan sheet.

In column D, the formula checks the Table number column (C), and if the result is “—” then it returns the next available ID number for that guest.

**=IF(C2=”–“,SUM(MAX(D$1:D1),1),””)**

Those numbers will be used to create the drop down list of guests.

In the drop down list of guests, the guests who are already assigned to a table will be shown at the end of the list. The formulas in E and F created the numbering system for those guests

The formula is column E returns the next available ID number for each assigned guest.

**=IF(D2<>””,””,SUM(MAX(E$1:E1),1))**

The formula in column F adds those numbers to the maximum number in column D (unassigned guests).

**=IF(E2=””,””,SUM(MAX(D:D),E2))**

In the screen shot below, the “END OF LIST” item has the Unused ID of 16, and the first assigned guest (Dan), has the Used ID number of 17.

In column H, there is a list of numbers that were manually entered – from 1 to 25. That is the number of guests, plus 1 for the “END OF LIST” item.

In column I, a formula returns the guest names with the matching numbers.

**=INDEX(B:B,MATCH(H2,IF(H2<=MAX(D:D),D:D,F:F),0))**

The formula will return a name from column B, based on the number in column H.

- If the value in column H is less than or equal to the highest number in column D (unassigned guest IDs), it looks for a match in column D.
- Otherwise, it looks for a match in column F (assigned guest IDs).

NOTE: If you add more guests, add a number for each guest, and copy the column I formula down to the last number.

The list of names in I2:I26 is a dynamic named range – NamesUse. That name is the source for the data validation drop down lists on the TablePlan sheet.

This OFFSET formula is used to define the named range, and is based on the highest number in column H.

**=OFFSET(Lists!$I$1,1,0,MAX(Lists!$H:$H),1)**

If you add or remove numbers in column H, the NamesUse range will automatically adjust in size. Just remember to fill in all the numbers in column H – don’t leave blank cells after adding guest names.

Click here to download the new Excel seating plan with charts workbook. The zipped file is in xlsx format, and does not contain any macros.

______________________

]]>

There’s a budget tutorial on my website, and it shows how to set up a workbook with forecast and actual budget amounts, and then calculate the variance.

It has a traditional layout, with months across the columns, and budget categories down the side.

There are separate sheets for the Forecast amounts and the Actual amounts – and their layouts have to be exactly the same.

Here’s the Forecast sheet, where you enter the amounts in the green cells.

In that sample file, I checked the file properties, and that workbook was created way back in September 2002! It was time for a new version of the budget workbook.

There’s a new download file now — #2 in the download section. This version takes a different approach for entering and reporting the budget amounts. Here’s what the new report looks like, and there are details below.

Instead of entering the Forecast and Actual amounts horizontally, the new workbook has a named table for data entry – **tblInput**.

Enter the Forecast amounts there, and later, add the Actual amounts, when they’re available.

The data entry table also has columns with formulas to calculate the Year to Date (YTD) amounts, and the Variance.

For YTD, the actual amount is used, if it has been entered. Otherwise, the forecast amount is used.

**=IF([@Actual]=””,[@Forecast],[@Actual])**

For Variance, the Forecast is subtracted from the Actual, or zero is shown, if there is no Actual amount.

**=IF([@Actual]=””,0,[@Actual]-[@Forecast])**

For Variance %, Actual is divided by Forecast, and 1 is subtracted, or zero is shown, if there is no Actual amount.

**=IF([@Actual]=””,0,[@Actual]/[@Forecast]-1)**

On another sheet (Lists), there is a named table (**tblRpt**), with the five report types that will be available.

In the adjacent column, the number format for each report type is entered.

NOTE: The report types are an **exact match** for the columns in the data entry table.

There is also a pivot table based on the Report types table, with the Reports field in the Rows area.

- The value in cell I2 will be used in a “Report” formula, to determine which values to show in our Budget report.
- The pivot table name was changed to
**ptRptSel**

In a cell named **FormatSel**, a formula returns the format for the report type in cell I2

**=INDEX(tblRpt[Format],MATCH(I2,tblRpt[Reports],0))**

That value will be used in a macro that formats the final report.

The next step is to insert a Slicer, based on the Report Type pivot table. This will be used to select which values to show in the final report.

Change the Slicer settings to hide the headings, and set it to show 5 columns

Adjust the Slicer’s size, so the five Report Types are visible. The Slicer will be moved to a different sheet later.

Back on the Data Entry sheet, the final column in the input table is “Report” This column has a formula that gets the value based on the Report Type in cell I2 on the Lists sheet :

=INDEX(tblInput[@[Code]:[Var%]], MATCH(Lists!$I$2, tblInput[#Headers],0))

Currently, cell A2 show “Actual”, so that value is returned in the Report column.

To create the budget report, insert a pivot table, based on the input table.

- Put the Category, Code and Item fields in the Row area, Month in the Column area, and Report in the Values area.
- Change the pivot table name to
**ptRpt** - Move the Slicer, so it is above the pivot table.

If you click a Report Type in the Slicer, and then refresh the pivot table, the values will change. We’ll be adding a macro, to automate that.

We’ll add a macro to update the pivot table’s Report values automatically, when we click a Report Type on the Slicer. The macro will also format the numbers, based on our lookup table.

To add the code:

- Right-click the Lists sheet tab, and click View Code
- Copy the following code, and paste it onto the code module

Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) Dim pt As PivotTable Select Case Target.Name Case "ptRptSel" Set pt = Sheets("Report") _ .PivotTables("ptRpt") With pt .RefreshTable .PivotFields("Sum of Report") _ .NumberFormat = Sheets("Lists") _ .Range("FormatSel").Value End With End Select End Sub

After the macro has been added, go back to the Report sheet, and test the Slicer.

- Click a report type in the Slicer, and see those values in the pivot table.
- The Report column in the data entry table calculates which value to show
- The macro refreshes the pivot table values, and applies the number format.

In the animated screen shot below, you can see that the blue text changes, when the Slicer is clicked. That cell is linked to cell I2 on the Lists sheet, to show the selected Report Type.

In the sample file, there’s another Slicer too — use it to show or hide the zeros on the Budget Report sheet.

That slicer is based on another named table and pivot table (**ptZeros**) on the Lists sheet.

There’s an extra section added to the code on the Lists sheet, to show or hide the zeros on the Budget Report sheet.

Case "ptZeros" With ActiveWindow Select Case Range("ZeroSel").Value Case "Hide 0s" .DisplayZeros = False Case Else .DisplayZeros = True End Select End With

To get the Budget Report with Value Selector workbook, go to the Budget Variance page on my website.

This example is #2 in the download section. The zipped file is in xlsm format, and contains macros.

__________________________

]]>

To show you the problem, here’s a screenshot of the lookup table, and the VLOOKUP formula. The formula in cell D5 says that the blue cell (A2) and the green cell (D2) are equal.

However, there’s a VLOOKUP formula error in cell E2. Instead of returning the product name, “Blue shirt”, the result is #N/A.

If you love an Excel challenge, click here to download the sample file, and see if you can fix the problem – it’s a tricky one!

- If you’re not sure where to start looking for the problem, there are VLOOKUP troubleshooting tips on my site.
- The sample file also has a sheet that shows my troubleshooting steps, and another sheet shows my formula that fixes the problem.

**WARNING** – The troubleshooting steps for the VLOOKUP formula mystery start in the next section.

Stop reading here, if you don’t want to see how I fixed the VLOOKUP formula error.

.

.

.

.

.

Usually, this type of VLOOKUP error occurs when one value is a real number, and the other is text number. See the details for that type of Number/Text problem on my website.

To test the numbers in the sample file, I used the ISTEXT and ISNUMBER functions. The screen show below shows the results of that test, for the values in A2 and D2.

Both cells contain text, not real numbers, so a Number/Text issue isn’t causing the errors in this workbook.

Another common cause for VLOOKUP errors is extra characters in one of the cells – usually extra space characters.

Using the LEN function, I checked the length of the string in each cell.

There are 7 characters in A2, and only 5 characters in cell D2

The TRIM function will remove leading, trailing, and duplicate spaces.

- In cell D16, I used TRIM on the value in A2.
- Then, I checked the length of the trimmed string.

There was no change, so the extra characters are NOT normal space characters.

The next step is to figure out what those extra characters are.

- We know there are 7 characters in cell A2, so I listed the numbers 1-7 on a worksheet.
- In the next column, the MID function extracts one character at each position.

The numbers from cell A2 appear in positions 2-6, and there are hidden characters at the start and end of the string.

In the next column, I used the CODE function, to see what each character number is. Some characters, such as non-printing characters 0-30, can be removed with the CLEAN function. Let’s see if our hidden characters are one of those.

No, Excel says that cells D2 and D8 contain character #63.

There isn’t a non-printing character with code 63 though, so what is happening?

I typed 63 in cell D10, and used the CHAR function to see the character with that code number. Hmmm…it’s a question mark.

So those hidden characters are not really questions marks, but Excel is confused, and returns that code number anyway. (I found that clue on the Mr. Excel forum).

- CODE and CHAR use the basic ANSI character set in Windows, which has a maximum code number of 255.
- The hidden characters are probably from a different character set, and have a code number greater than 255

Whatever they are, we can’t use CLEAN to remove those hidden characters.

To fix the formula, we don’t need to know what those hidden characters are. However, I was curious to find out if they were both the same character, or two different characters.

- To test that, I used SUBSTITUTE, to replace any instances of the first hidden character, with an empty string. That reduces the string by 1 character
- When I used SUBSTITUTE for each of the characters, both hidden characters were removed from the string.

If you really need to identify one of these strange characters, use the AscW function in a macro, or in the Immediate Window. This bit of code will show the character number for the first character in the active cell.

Sub GetCode() Debug.Print AscW(ActiveCell.Value) End Sub

With that code, I learned that the hidden characters in this file are characters 8237 (cell C5) and 8236 (cell D5)

To fix the problem, I set up a “hidden character extraction range”.

- In cell H2, a sample code was pasted from the lookup table
- In cell I2, a LEFT formula pulls out the first character: =LEFT(H2,1)
- In cell J2, a RIGHT formula pulls out the last character: =RIGHT(H2,1)

NOTE: You could put this information on a hidden sheet. I left them on the same sheet as the VLOOKUP, so it’s easier to see how they’re used.

Then, combine those hidden characters with the product number in the VLOOKUP formula in cell E2, to get the product name:

=VLOOKUP(**I2 & D2 & J2**, $A$2:$B$6,2,0)

To see all the details on the VLOOKUP Formula error problem and fix, download the sample file.

The zipped file is in xlsx format, and does not contain any macros.

__________________________

]]>

In this example, select a region name in column B. Then, when you click the drop down arrow in column C, the list just shows the customers in that region.

There are a couple of benefits to dependent drop down lists:

- It’s easier to pick a customer from a short list, instead of the full list
- It encourages valid customer entries. (Data validation isn’t bulletproof – there are ways to get around it)

Get the sample file, and see the complete setup instructions on my website.

There are two lists used in this dependent data validation technique.

- The Region drop down is based on the Regions list.
- The Customer drop down shows the customers for the selected region, from the Region/Customer table

The dependent drop down for Customer uses OFFSET, MATCH and COUNTIF to find the customers for the selected region.

**=OFFSET(RegionStart, MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1)**

It finds the first instance of the region name, and gets customers from the next column, based on a count of the region name. In this screen shot, East is in the 8th row, and the six customers from that region would appear in the drop down.

See my website, for more details on how the formula works.

For the OFFSET formula to work correctly, the lookup table MUST be sorted by the Region column. If the list is sorted by customer name, the East region list would show the wrong set of 6 names.

In the original version of this technique, you had to remember to sort the list by region, after making any changes to the lookup list. There wasn’t a warning system to alert you to problems.

To help avoid errors, I’ve created a new sample file, and it has formulas to check if the region names are in A-Z order.

There’s a new column (SortCheck) in the lookup table, with a formula to check the order.

**=IF(A3=””,0,–(A3<A2))**

If an item is out of order, there is a 1 in the row above it. The “East” in A5 is less than the “West” in A4, so cell C4 returns a 1, instead of a zero.

In a cell named SortCheck, a formula calculates the total for that SortCheck column.

**=SUM(tblRegCust[SortCheck])**

Another named cell, SortMsg, contains a typed error message that will be used in the data validation.

To show a warning in Excel drop down when necessary, I changed the Customer data validation formula slightly. The IF function looks at the total, and shows the SortMsg range, if the total is greater than zero.

**=IF(SortCheck>0, SortMsg,**OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1))

You’ll have to fix the list, before you can choose a customer name.

It’s easy to overlook a message that’s in a worksheet cell, but this error message is hard to ignore!

For other ways to create dependent drop down lists, go to the following pages on my Contextures site:

Get the warning in Excel drop down sample file, and see the complete instructions on my website.

______________

__________________

]]>