Connecting Knack to Excel

Hey there. My name is Dimitri. And in today’s video, we’re gonna dive into how to integrate Knack with Microsoft Excel 365 using Knack Flows. By the end of the video, you’ll be able to automate document storage and retrieval seamlessly from your Knack database to your Excel spreadsheets.

Challenges of Manual Data Management

Oftentimes, accounting or client management can be really hard to do inside of Excel spreadsheets. However, manually updating and syncing that data with a custom database like Knack can be time-consuming and prone to errors. The solution is using Knack Flows. By connecting Knack to Office 365 Excel using Knack Flows, an accountant can automate the syncing of financial data between their Excel sheets and a Knack database, ensuring that both are always up to date.

Setting Up the Knack Database

This allows for real-time updates and efficient reporting. So how is it possible to make this happen? Inside of Knack, you can create many different systems and databases. For example, inside a client portal that I’ve created—based on a template—I actually have an invoice database right here.

Understanding the Invoice Database Structure

You can see this database has a lot of different information, whether it be the tax rate, the equation, service fee, and much more. As you take a look here, we’ve got records, labor rate, service hours, and some formulas to go along with it. If I wanted to track this and track my invoices inside of an Excel spreadsheet, I absolutely could. All you need to do is go to Knack Flows.

Creating a Flow to Add Rows in Excel

Based on the setup right here, I’d simply go to “Create Flow from Scratch.” I’m going to name it “Add Row to Excel Spreadsheet.” Now I can add a trigger—“When this happens in the app.”

Configuring Triggers for Data Syncing

So, say for example, in Knack, when a record is created, I’ll select the client portal and the table would be invoices. Press Continue.

Linking Excel Account for Data Integration

Inside of Excel, I’ll add a row and then link my Excel account. Now I’ve added my connection here.

Selecting the Right Spreadsheet for Data Entry

I created an “Invoice Tracker” in Microsoft 365. I click on it, and now it’s added. I just need to use this worksheet. Let’s click on that.

Aligning Columns for Data Consistency

Press Continue. Now, as you can see, there are a bunch of different columns, and we might want to add them based on preference. To make this easier, I’ll do a quick CSV export to get the format down. Or I could import a CSV into Excel, or grab the column names I need.

As you can see, this is all set up to align with the database. I’ll line up the columns with the respective fields from Knack. To keep it simple, I’ll use fewer columns here—tax rate and grand total to end it out.

Testing the Data Flow

Then I’ll press Save. Let’s press Start and run a test. I’ll go into the data section, go to invoices, and simulate a new record. Labor rate is 100, service hours are 2, and service fee is 350. Subtotal, tax rate is 0.08, and I press Submit.

Now, if I check my Excel sheet, you’ll see it adds all of those fields.

Benefits of Using Excel for Invoice Tracking

Really cool. So, say someone fills out that form in your Knack app, whether a public user or the accountant, this makes invoice tracking easier—especially in Excel. Let’s be honest, accountants live in Excel.

Exploring Additional Flow Options

You can also create flows for when a Knack record is updated. Select the client portal and the invoices table again. You can add more Excel options here like “Update Row.” A little back-and-forth update system works great.

Either of those will definitely help you out if you’re syncing data for an accountant or prefer Excel as an end pass-through of data.

Conclusion and Call to Action

With that being said, thank you so much for watching this video. Please make sure to try out Knack and get started today. Thanks again, and we’ll see you in the next one.