Excel can be an amazing tool! It’s feature-rich and extremely versatile. That versatility also makes it rather difficult to use. Excel has only grown over the years, too. Microsoft has tried to make it easier to use, and to their credit, they did a good job. There are three great features in Excel, though, that no one ever talks about. So, without further ado, here are three professional-grade Excel tips that every user should know.
Table of Contents
Excel Tip #1: The Search Bar
Excel tip number one: use your search bar.
The search bar in the Microsoft Office suite has become much more powerful, especially for Excel. It used to be that using the search functions in Office would only net you some built-in help articles or templates from the Microsoft Store. That’s no longer the case.
The new search bar can find any and everything Office-related. If you need to learn how to use a pivot table, type that into the search bar. If you forget a formula, the search bar will give it to you. If you can’t find a setting, the search bar will find that as well.
The new search bar in Excel has become very powerful. Don’t be afraid to try using it.
Excel Tip #2: Save to OneDrive
This is more of an Office tip than an Excel tip, but learn how to utilize OneDrive.
Many businesses don’t push the benefits of OneDrive enough. OneDrive is an excellent tool to both share and backup files. One of the more powerful features that OneDrive offers, though, is version control.
OneDrive can track changes in your files. It also can keep older copies of files. This is by far one of the best ways to protect your data. It’s also one of the easiest tools to use.
Configuring OneDrive isn’t difficult.
If your organization hasn’t already set it up on your Windows profile, you’ll find a configuration wizard on your PC. Just click the Start Button in Windows and type in OneDrive. Windows Search will find the configuration wizard for you.
That wizard will create a folder in your user profile labeled OneDrive. Once this folder is created, make sure to save any documents to that folder. You can also drag and drop existing documents to this new folder to sync to OneDrive online too.
Once your documents are in that OneDrive folder, you can view them online. You will need to go to the OneDrive website and login with your Office 365 account information. If you have Office 365 through a business, it will most likely be your business email account.
Once your files are saved to OneDrive, the online syncing app will automatically start working. Anytime a file is changed, OneDrive will track that change and make a new copy of that file. This means that if you get struck by Ransomware, you can easily roll back your files to a working version in OneDrive and restore them. If one of your Excel files stops working properly, then you can find the last known good copy in OneDrive and restore it.
Of course, all of this only works if OneDrive is configured on your PC and you are saving data to that monitored sync folder. So, make sure you are doing this.
Excel Tip #3: Get Data Functions
Excel tip numero tres is to learn how to use the “Get Data” function.
The new ‘Get Data’ functions in Excel are great tools for CPAs, programmers, Sysadmins, doctors, etc.… They are great for everyone. Here’s why.
The Get Data functions will pull data in from a ton of different sources. You can pull data from a website, a JSON file, a text file, an XML file, etc.… Excel does a great job of parsing this data, too.
On import, Excel will do its best to separate data as well as it can. Just in case, though, Excel will walk you through a quick wizard to make sure that it is using the proper demarcation points before converting that data into a spreadsheet.
Excel Tip #4: DBAs and App Developers Take Note
This tool is amazing for converting large data sources into structured files to import into databases. It can be challenging to convert JSON or XML data to database tables. The Get Data functions are great for this!
Import your data using the Get Data functions in Excel. Excel will turn the data into a 2-dimensional spreadsheet conceptually like a database table. Make sure your data is proper and normalized.
Most database admin panels have a way of importing CSV files into databases. So, save that table you just made as a CSV file. Then access your DB admin panel, such as PHPMyAdmin, and import the data into your table.
We hope you enjoyed these Excel tips. For more Excel tips and tricks to make your life easier, please send us a message.