As a land pro, you probably work with tons of data, making spreadsheets integral to your day-to-day workflow. Luckily, Microsoft Excel has a lot of features that allow you to go deeper than just displaying data and doing simple calculations.
While there are entire courses dedicated to mastering MS Excel, below are five key features that can help you level up from novice to master in no time!
Getting your data into Excel isn’t difficult, but once it’s there, then what? Depending on your needs, you may need to adjust how your data is sorted, create a filtered view, or perform other helpful manipulations. Here’s an example of how to filter data by city, then zip code:
As you can see, sorting and filtering are pretty straightforward, but there are some things to remember. For one, only the data selected will be sorted, so make sure your whole data range is selected if your data needs to remain associated across rows.
Sometimes, you need to hide blocks of data that aren’t easily filtered. For example, as a land pro, you may have a dataset that contains complete parcel information, but if you only need to see a subset of that data for a specific report or analysis, you can hide the data you don’t need to see without deleting or losing it. Here’s how:
Of course, when working with thousands of rows or columns, scrolling away from the headings can cause you to lose track of what data is what. To prevent that, freezing panes is a quick and handy trick to keep your header row or column visible at all times while still scrolling through the data. Freezing panes doesn’t just work for the top- or left-most panes, you can freeze a pane anywhere that makes sense for your data.
Now that we’ve looked at ways to sort and filter data manually, it’s time to explore data formatting. You can always manually format your data by selecting the cell and adjusting colors, sizes, etc. But doing that consistently and for a large dataset is inefficient, especially when conditional formatting can handle that work for you! Conditional formatting is rules-based, so you set the rules, aka conditions, to cause data to display a specific way. An example in accounting would be: displaying numbers in green for-profit and red for loss. For our example parcel dataset, let’s format by the lot square footage:
You can set conditional formatting rules based on numeric values, specific text, or even dates. There are also many prebuilt rules or you can create completely custom rules, from basic to complex.
Formulas and functions are one of the most important and useful features of Excel. There are many types of functions for manipulating both mathematical and text-based data. Of course, we can write a whole book on how all of the functions in Excel work, but we’re just going to focus on the basics so that you can explore more advanced functions with ease.
Below is a simple mathematical operation as a function, where we multiply the value of column A by column B and display the result (via a function) in column C:
Declaring a function is always handled the same: you start with =FUNCTION followed by your arguments in parentheses. The arguments are often the specific row/column ranges to be calculated. You can also just do math directly by skipping the FUNCTION and just placing your equation in the data bar. For example, to multiply you would use: =(arg1 * arg2). Let’s see it in action:
Functions can do far more than just basic math, however. From advanced math, logic, and statistics to data lookups and transformations, this is one of the most powerful features in Excel. Now that you’ve seen the basics, we recommend spending some time practicing with more advanced functions to really stretch your skillset.
You may find yourself working with data that needs to be reformatted. If it’s a small dataset, this can be done manually, but when dealing with thousands of records, that’s not a good use of your time or energy. If you need to break up data formatted as last name, first name into separate columns, or if you need to break up an address into individual fields, here’s how:
Excel can split or group data, remove duplicates, and more. Don’t be afraid to spend some time on the Data tab exploring the many ways you can easily reformat your data!
Raw data is great but sometimes we need to get visual. You can format your data into tables that can then be displayed as a variety of different chart or graph styles. As a land pro, you may need to show visual data relationships, such as the upward trend in land sale prices for a region, or the relationship between land features and saleable value. Here’s an example using assessed and taxable values:
As you can see, charts and graphs are a great way to visualize and illustrate your data. You can also change formatting and styles, or the chart type to best represent your analysis.
In Summary
As a land pro, you will work with all kinds of data throughout your career, and mastering these foundational Excel skills will set you up for success.
If you found this helpful, be sure to sign up as a member (for free!) for more personal and professional development tips straight from experts and fellow land professionals.