Video: Navo-Friendly Spreadsheets

In this video we’re going to talk about what makes a spreadsheet appropriate for Navo. Not all spreadsheets work for Navo, and it’s important to know what kinds work, and what kinds don't. We’ll also talk about ways in which you can clean up your spreadsheet before loading it into Navo - and if you have to.

First off, Navo is designed for spreadsheets filled with what we call detail data. That means that each row of your spreadsheet describes a single something. That could be an order, a shipped case, a phone call, a CD, whatever. What that something is doesn’t really matter, so long as every row describes one. Of course, an “Order” could be for a dozen eggs - but it’s still one order. Every row has to describe the same kind of something. You can have orders for different types of products, but you can’t have an Order on one row and an Employee record on another!

In the example here you’ll see that each “Invoice” is actually on several lines. That’s okay - it just means that each row of this data set is a different line item in an invoice.

At the same time, each column has to describe a single characteristic of all of the rows. For example, you could have a column of names, phone numbers, or the count of items that the row represents. What’s important is that every cell in that column has the same information about its row. You can’t have a column that’s Names on some rows and Phone numbers on others.

Each column should have a single header, and should have a unique name. For example, let’s say you’ve got dollars and units for this year and last year. It’s easier for a human to read it with a group of columns for Years with individual columns for “Dollars” and “Units”, but Navo won’t know what to do with that. It’s better to have four individual columns - “Units This Year, Dollars this year” and so forth.

Next up: avoid Subtotals! Navo is going to add up every row, so if you have subtotals they’ll get added in - and thus double counted. Navo doesn’t have a good way to tell if a row is a subtotal row, so you’re better off if you leave them out in the first place. If you’ve got subtotals, remove ‘em if you can.

Next: Keep your numbers numbers. If you put text in there, like “N/A” then Navo will think that’s a column of strings with some numbers in there.

Blank is okay - it’ll get treated like zero, but anything else will confuse it. Navo will pick up on formatting stuff like dollar signs. It’ll keep those if every row with a number in it has one, and it’ll ignore commas (or whatever your thousands separator is).

Navo can deal with header information. For example, if you have a couple of rows of stuff that maybe includes the title of the spreadsheet or “Created by” information, Navo will see that and skip it. Navo will also ignore formatting information like cell color, justification, borders, that sort of thing.

The best data set has a mix of dimensions - interesting ways to break down the data like “Customer Name” or “Sales Region”, and summaries - things to add up, like “Dollars” or “Cases”. But you don’t actually need summaries. Navo will give you the number of unique values for any dimension at any time. Sometimes, the number of customers you sold stuff to is really all you want to know.

That’s about it! Remember, the most important thing about Navo is that it’s read only. Not sure if a data set is appropriate? Try it! The worst thing that happens is that it doesn’t work.

Thanks for watching!