What’s Your Chart Signature? Graphing a name in Excel

Have you ever wondered: how does my name look like if plotted in Excel? Well, wonder no more. In this post, I’ll show you how to enter any name (or any string of characters) and plot it in dynamically to make your unique name come alive as a chart.

To make it fully dynamic, I entered a name in a cell, then I extracted each letter from that name (no matter how long, spaces, dashes, in-between) and put them in their individual cells in subsequent rows…each letter in a cell, in its own row. Then I obtained the ASCII code for each of the letters (or characters such as space, period, dash, etc. in the name) and plotted them in two different types of plots for fun. I demonstrate them below.

However, you may be asking: what if there are Unicode characters in the name? No problem! It takes a little bit of understanding and a teeny bit adjustment to the formulas and I’ll demonstrate that below too. So, let’s get started with some examples starting with ASCII letters, then we’ll deal with Unicode letters.

Let’s say we want to see what a chart for “Arthur C. Clarke” looks like. First, I entered the whole name as text in a cell (say, B1). Then in B2, C2, I made 2 columns: ‘Name letters’, and ‘ASCII Code’. Next, in B3, I enter the formula to extract the first letter from the name “Arthur C. Clarke” entered in B1. So, my formula is =MID($B$1,ROW()-ROW($B$3)+1,1). Of course, I could have entered 1 as the second argument in MID function, and then in the next row, manually enter 2 as the second argument and so forth but that wouldn’t be dynamic, would it? By using ROW function, I can get the current row number wherever the formula is entered…therefore, by deducting the starting row number (B3) and adding 1 to it, I’m basically creating a counter starting from 1 and incrementing by 1. All this, without using any macro so it’s fully portable without any security issues when shared as a standard XLSX workbook.

Next, drag this formula down to as many rows as needed so extract all the letters from the name in B1. Finally, in the column ‘ASCII Code’ starting in C3, I enter the formula CODE(B3) to extract the ASCII codepoint for the letter in B3. Again, drag this formula down to as many rows as needed for the name. An example range or table should look like this:

Now we’re ready to actuall plot this range or table (it’s up to you if you want to use a range or table, it makes no difference other than the usual convenience of table formulas automatically expanding/contracting as you change the letters). I chose to plot a Radar and a Line chart to visually showcase the name’s unique signature. This is what they look like once you create the charts:

Radar chart for: Arthur C. Clarke

Line chart for: Arthur C. Clarke

Pretty cool! [Does his chart signature kinda look like an owl’s face with its beak hanging down?] Using this concept, I can even plot unique and fun charts…for example, here’s a heart-shaped chart (I have added data labels to show the letters I used to create it so you can too):

NOTE: If you want a real mathematical way to draw a smooth heart-shaped plots, see my post here.

Now it’s time to tackle Unicode characters. Let’s take this example:

The name “Alex” is completely non-ASCII and entered using Unicode symbols. So the above functions CODE and MID as used with ASCII aren’t going to cut it.

To deal with unicode, we have to use UNICODE() to extract the unicode character’s codepoint first.
But since it’s a string, we have to extract each character from the entire Unicode string one by one, again, using MID function. However, unlike with ASCII characters, the the arguments in MID() will be different as Unicode is not 1 byte, rather occupies 2 bytes! So, in the 3rd argument of MID() we pass 2 for Unicode instead of 1 as we did before for ASCII characters. Also, because of each character’s byte-length, the 2nd argument, which is the starting position of character to extract from the string will start at 1 (as before), but then increment by 2 instead of 1. The image below shows my table layout with two columns: ‘UNICODE’, ‘Glyph’ and their respective formulas I used per row (on the left of the table, and to the right of the table). The unicode name is entered in B57.

Now all we have to do is chart this as radar and a line chart (or whatever you prefer) to get “Alex”‘s unique chart signatures…which look likes this:

P.S. The chart titles are also dynamic as I entered the cell reference where the name cell is entered into the title element’s formula.

Related: