Use Excel to generate charts with real (or fake) data, then use them in Sketch

For just over the past year, I’ve been on a team at IBM, designing financial dashboards for executives. Like many other product designers, a lot of my work happens within Sketch, and thankfully, Sketch is getting better and better at making it possible to resize complex groups and utilize symbols to speed up UI design.

One thing that still can take a long time is designing graphs and tables, because they simply have a lot of pieces. I just came across an article titled “Designing Data with Sketch,” which offers quite a few helpful tips on making the process of designing charts and tables more bearable. I’ve discovered most of these tips on my own through trial and error and Googling, but one of them caught my eye: “Creating Line Charts: Use Numbers or Excel.”

This is amazing since it saves an enormous amount of time “faking“ a nice looking graph within Sketch and allows you to design with real data. Just use your favorite spreadsheet application to create a line chart and export it as a PDF. When opening the PDF with Sketch, you can select each individual element of the chart, since they are all exported as vector graphics and organized in layer groups.

One of the major shortfalls of designing charts in Sketch is that often, it’s an extremely tedious thing to make a chart realistic (or even a passable placeholder) by matching it to real or realistic data. However, this method has the potential to help with two things:

  1. Save you from spending thought cycles coming up with random numbers that look realistic on a chart — even for a placeholder, you’ll just need to know an appropriate range. Then, you can get a pre-built line for use in your own chart design.
  2. If you have actual data, you’ll be able to make sure your graph works for real data. Awesome!

How to do it:

Assuming you’re a total Excel n00b, like I am, here’s a step-by-step of how to do this trick.

1. Set up two axes in Excel

Primary unit of measurement in Column B, and your time range in Column A

Plot your primary unit of measurement in Column B (e.g. Money), and your time range in Column A (e.g. months of the year).

2. Generate random numbers (if you don’t have real data)

Click a cell, then use the top content bar to enter in this function:

=RANDBETWEEN(valueX, valueY)

This will generate a random number between the two values you fill into X and Y. For example, if I think that the realistic numbers might range from 75 to 250, my function will be entered like this:

=RANDBETWEEN(75, 250)

3. Copy/Paste this value into the remaining cells of Column B

Just highlight those cells and Paste.

Fast!

4. Make the chart

Go to the “Charts” tab and select the appropriate line chart. In this case, that will be simply “Line.”

Resist the urge to get too fancy

5. Boom! You made a chart. Now save it as a PDF.

Making the chart is that simple! Now you just need to get it into Sketch to actually use it. Luckily, that’s easy, too. You can export this as a PDF, which will have these numbers as text, and the lines as vector strokes.

Money!

Go to File > Save As, and then select PDF as the format to save this in.

Be sure to save this as a PDF in Excel

6. Open the PDF in Sketch, and find the line of your chart

What it’ll look like at first

If things go the same on your end, you’ll initially see this mess when you open your chart in Sketch.

When I open the saved PDF in Sketch, I see a mess, because for some reason, Sketch interprets every stroke with a massive width. Fear not! The chart line you need is pretty simple to find in the layers menu.

Search for the stroke you need.

7. Optional: make things look normal

Zoom way out, then hold Command while you drag your mouse over everything. Then, enter the number ‘1’ into the Borders Thickness control field. This should make your messy chart into something that looks a lot more like what you had in Excel.

Set Borders to 1px to fix the rendering

Copy/Paste that line where you want it, and go!

If you’ve already got some assets built up in Sketch, this should be pretty quick work. Just remember to scale the line to properly fit the match the data you have in Excel, or you’ll be back to something completely unrealistic.

So realistic! Nicely done.

And there you have it: away to work between Excel and Sketch with relative efficiency. Until someone makes a terrific Sketch plugin for data visualization design, this should prove helpful in your data design endeavors. And if there is a Sketch plugin that does this really well and I don’t know about it, please let me know!

If you want to learn a few more useful Sketch tips for data design, check out the original article that helped send me on the path to figure this out: Designing Data with Sketch

Show your support

Clapping shows how much you appreciated Stephen Nixon’s story.