Airtable is a spreadsheet cloud program with elements of a database. Unlike Excel or Google Sheets, it’s super user friendly, visually intuitive, and flexible for collaboration. I used in my last two jobs as a tool to organize a wide range of data.
Now, I use it in my personal life to track entertainment and plan my yearbook.
It’s really changed the way I see leisurely tasks and events in life. So, I thought it would be helpful to share with my readers different ideas of how it might also be beneficial in your own life.
I listed Airtable before in my “Top Personal Management Programs” post. This post is a more detailed overview of some example ideas for bases.
The Unique Features of Airtable
If you’re familiar with the basics of Excel or Google Sheets, then Airtable appears like nothing new. So, what’s so great about Airtable?
For starters, the program is easy to learn.
In Excel you have to search through different menus and remember a complicated formula for a simple lookup between tables or to create a checkbox field. It seems like every special formatting in a traditional spreadsheet program has a high learning curve, and you’ll need to Google all the tricks.
Worse yet, you’ll need to pay for and complete Excel coursework for the world to really respect your knowledge.
By contrast, Airtable offers free video tutorials. They don’t try to hide anything.
And, the field (columns) each have simple, designated purposes. When you create a field, you add it with a preset ruling, like a text field, currency, date and time, formula, single select, etc…
Two unique field options are “Link to Another Record” and “Attachment”.
Link to Another Record
As the name implies, Link to Another Record pulls the primary field from another table and links them together. One application of this tool would be for financial tables. Your “Expenses” and “Sales” tables you might want to link together to use with a simple profit formula.
Related to the Link to Another Record field is the Rollup field. It works by looking up data within all of the linked records and commuting a formula. For example, you could have all your monthly purchases linked to an expense table and use the Rollup field to display the average pricing of a specific item type.
There’s a lot of other special formulas that work with linked records to simplify all kinds of data. If you’re used to using other program, the language is a little different, but you can use Airtable’s formula quick reference page. Also, check out Youtube for visual examples of different functions, like the channel Gareth Pronovost.
Attachments
The Attachment field lets you attached file types from your local desktop or from a number of cloud services. You can add a picture of a client or link a completed document referenced in the record.
I am highlighting this field because it’s unique from other programs I’ve seen. You could just paste a Google Drive link of the file in a text field, but adding it as an attachment file lets you create an icon in the gallery view. Plus, the Attachment field allows you to pull from an email or a Google search directly.
Views, Filters, + Collaboration
Moreover, Airtable is a great visual program because it allows you to easily filter, group, and sort records in saved views. If you wanted, you could have a view of a table that groups records by a certain date range, filters out any from April, and hides weekends.
You can do the same things in Excel or Google Sheets, but Airtable allows you to set view permissions with different collaboration settings in your team. If you’re a team of writers for example, your records for web pages can filter so different writers only see the tasks within the job they need to complete.
The writers sees the records when their status is set in the “Writing Stage”. Then, when they change that status to “Draft Complete”, the filter settings move it to private view where only the editors see it. And so, and so on… until the project is published.
Collaborators can also chat and tag each other within the expand record option.
Airtable Paid Features
Airtable has the downside of being limited by a pay wall, though all the essential features are free to use and they give you a generous amount a storage space.
The biggest paid feature is a set of tools called Blocks. They act like standalone summary functions for the whole base. I haven’t used many because the free version is enough for me, but I understand Airtable has one for creating designed report pages, pivot tables, world clocks and countdowns, charts, etc…
Additionally, Airtable has a number of Zapier and API integrations. They involve some sophistication of programing. We used them briefly in one of my digital marketing jobs. You can explore all the fancy stuff yourself if you need.
Airtable is a fun way to sort and track life. Here’s 10 effective ways to use the program in your financial life:
Airtable for Financial / Small Business Needs
You’re probably used to the idea of spreadsheets being used for mass numerical data calculations. These bases should be easy and familiar:
1 – Job Hunting
If you’re looking for a job, as I was last year, Airtable is a great way to track applications.
For this base, I created three linked tables: Jobs, Companies, Contacts.
The Jobs table is a list of all the job posting you’ve found. You can set a Single Select field for the source of the posting for easier grouping (Indeed, Monster, Criaglist, Facebook, etc…). Then, I would another Single Select field labeled “Status” with the items Not Applied, Applied, Interview Scheduled, Awaiting Response, Rejected, and Job Offered.
Next, add a Date field with the date you submitted the application. You can do several things with this field, like addings a time difference formula to the present or when the company follows up with the application. You might even want to combine several time difference formulas and see the exact time it took the company to hire or reject you from each step of the status.
72 hours to first contact you… 12 hours to schedule an interview… 48 hours to give an offer…
All that time adds up, and it’s helpful to have documented the response time of a company. It might be indicative of a sluggish HR or a mismatch in your qualifications.
You might also want to add to the Jobs table the posted wage, the type of job (remote, part-time, full-time), and the application URL.
Then, link this table to the Companies table. In this table, add fields pertinent to the companies that have posted the job, like the location address, website, and phone numbers.
In the Companies table, you can see the average wage of a certain job title between various companies. This is a good way to really judge the local market. Also, you can easily compare the commute distances to the job locations.
Finally, link this table to a Contacts table. As you search for jobs, you’ll be contacted by recruiters, managers, HR, and any “in” at the company. Have all these people on one page to list their titles, emails, and phone contacts. Then, you might want to upload any documents or emails related to those people in an Attachment field. You should also link this table back to the Jobs table in a record called “App Reviewed By” or “Interviewing With”.
While not necessary in my opinion, you could also create an “App Docs” table to store your resume, email templates, custom cover letters, and work sample links. For me, it’s easy when everything is in one place, but you might already have a specific folder on your desktop for these items.
That’s the gist of a basic job hunting base I’ve found effective in my own career searching. Of course, tweak it as needed for your field.
2 – Recruitment
Once you got the job or own a small business… you might be handling the opposite of the Job Hunting base: Recruitment!
The idea is to simply have a Jobs table linked with an Applicants table. Again, have the application date for the candidates. You can use a Lookup field to show the post date and use it with a date difference formula of their application submission.
You can also assign numerical weight to the candidates’ strengths and weaknesses for some kind of cost-benefit analysis formula.
Bilingual, short commute, 5+ years experience, positive interview, clean background… those could all be weighted metrics for a sum formula in the Applicants table.
Then, in the Jobs table, the Roll Up function can average all those point values together or show the maximum score.
If you have a very low value of applicants, it’s possible your ad isn’t effective or there isn’t a hot market for the position. It’s important to try to stay as qualitative as possible to give you an objective outlook. By contrast, if you always go with your “gut feeling” in hiring, then you’ll get burned more often than not.
Remember, if you close a position because you made a hire or something changes, keep those applicant records! You never know when you’ll need the position again. I’d add a status “Applied Closed Position” and filter them into their own view.
3 – Budgeting
Setting and sticking to a budget is challenging for some people. Airtable makes it a little easier by auto calculating and displaying trends…
The budget base is the most complicated of my examples, so I’ve duplicated the personal one I use to embedded above for you to visually see (I’ve changed most of the values for privacy sake and to make the math easy for you).
If you like what you see, you can simple copy the base as a template.
If you need more help and a walk through, feel free to reach out to me.
The idea is to create an Expense, Income, and Budget Planning tables all linked together.
As the names imply, the Expenses and Income tables list values with dates of your payments and income sources. You could include an Attachments field to upload receipts, billing statements, pay stubs, checks, etc…
Continue by creating labels for each expense record through a Linked Record to a “Budget Planning” table. Include categories like entertainment, bills, food, and car per each week as in my example above.
Then, link your spending items per category from the Expense table and use the Roll Up field in the Budget Planning table to sum their total.
Next, link the Income table items. You primary field in the Income table is your dollar paychecks, but the program doesn’t recognize the links that way. So, you’ll need to also make a Rollup field the Income link’s dollar values.
If you’re paid hourly. you might have less or more hours each week that will fluctuate your Income values, so that’s why you need to link the Income table individually in the Budget Planning table.
It’s also possible you could be paid more than once per week (from multiple jobs or what have you), so a Rollup of the Income links makes the most sense, even if it seems redundant.
Then create a formula field that divides your spending by your weekly or monthly paycheck for each spending category represented by a percent.
The formula looks {Actual Expenses}/Income, where Income is a Rollup of linked Income values and “Actual Expenses” is a Rollup field of your Expense items.
Then, it should output something like 20% for food, 10% entertainment, 40% savings, and so until you have 100% total from each record…
Now, we have a breakdown, but we also want to plan a budget and compare the actual spending to it.
Create a percentage field and reasonable portion of 100% for each category on how you’d like to spend, keeping in mind you need a savings and emergency fund. Then, make a formula field that multiplies that arbitrary percentage field by your Income Rollup field. It should output the dollar amount you’re allotting yourself per month per category.
I also have a quick Spending Rollup table that calculates the values not yet budgeted for each week through a simple difference formula between a Roll Up total of all expense items with a Roll Up total of all income sources for each week.
So… all you have to do is stick to it!
Of course, if you see you over spending your actual compared to your planned spending, you need to adjust your percentage values for the following week to stay within budget.
I’d also create a static Fixed Bills table to remember deadlines and have links to quickly update subscriptions.
Then, get creative and add more tables if you want!
If you’re planning a large purchase, like a trip to Disney, you could make a “Saving Goals” table where you can divide up the costs of each project and use a linked rollup of each month’s savings to calculator when you’ve achieved enough money.
4 – Device Management
Asset management is a large project in many fields of business. I work with printers now as a technician (hence the fictional example), but last year I worked in trucking as an office manager. Instead of technology, I had a massive Airtable base that organized the maintenance, part installations, driver profiles for each unit in our fleet.
Having a list of asset data is all well and good, but you should expand it by linking tables of related staff and customers. Then, you can do all kinds of functions between the tables as needed. For example, you can roll up the average time each team member spends on a tasks as it pertains to a device.
In the trucking business, it was very useful to track dates in relation to maintenance. Sometimes, Goodyear tires might last longer compared to Bridge Stones on one truck compared to another. I would have to further analyze the road conditions of those drivers, their driving habits, and the quality of the tires used.
I cannot best tell you how to optimize this base as it depends on what you’re doing.
Having asset data available in one place with different views and link as you business operations dictate is key.
5 – Call Log
I took a lot of phone calls last year as part of my job in managing the office. I dealt with angry drivers, annoying sales calls, potential business clients, and sometimes government agencies for plating and toll passes.
It became frustrating to have to remember so much information in a short phone call and even embarrassing to have to call someone back to clarify something.
Eventually, I created a base with a similar setup to this example.
The Calls table has a Time Start primary field. It works with the Time End to in a time difference formula that calculates how much time you’ve used on that call. You can then roll up the average time spent per contact in the Contact table (and even roll up the times per company in the Company table).
You could also link a table listing tasks to the Call table. Certain calls might prompt actions that you’d want to track.
_______________
I’ve summarized 5 ideas to organizing Airtable bases for financial and small business needs.
I am looking forward to expanding this little series with base ideas in other categories, including entertainment, writing, and event planning!
If you learned something, share away and experiment yourself.
If you’d like to learn something, let me know and I’ll see how I can address your questions in future posts.