SHORTCUTS & FORMATTING
Using shortcuts and utilizing good practices for formatting is where you can gain a lot of attention from your peers or boss. Multiple times I put people in awe because I was so smooth at using Excel. If you didn’t know you can control all of Excel without even using a mouse, that’s my goal every time. Below in the template is what I use the majority of the time. As long as you know these you’re 75% there.
People have more trust in your data if your information is presented neatly, and that’s where formatting comes into play. ALWAYS take time to format your spreadsheet, because it will pay off in the end. You might say that formatting takes too much time. For one, you don’t have to spend too much time on it, just make enough time for it to be organized. Two, I have a personalized standard formatting guide that I use for myself. That way I always know what I’m going to do and what it’s going to look like. I would recommend creating one for yourself, and then continually changing it as needed. I also built my formatting guide using an Add-In so I could use keyboard shortcuts and cut down time.
It’s vital to know a few formulas in the workplace. No matter what department you’re in there are files that will be sent to you and it’s important to understand what’s going on. The most critical ones are listed below with my favorite links to learning them. All examples included in the template below.
- Vlookup Link
- Sumif Link
- IF Link
- Concatenate Link
- Index Link
Almost all information and data originates from some type of system. When it’s exported it almost always comes in a table format. Once you put it into a pivot table you make can sense of the data. This is a huge skill that all upper management knows. Pivots are easy to learn and are only as complicated as you want them to be. All examples are included in the template below.
- Design – Make your pivot neat and functional
- Slicers – Add slicers for easier navigation and appeal
- Calculated Fields – Create calculations within the pivot table
- Power Pivot – A Microsoft Add-In for a more robust Pivot Table. This is only needed when dealing with huge data sets and combining multiple tables. It’s a bit complicated, but if you need to make an impression this is what I would look into.
CONNECTING TO OUTSIDE SOURCES
When working with Excel there are going to be times when you need to pull information from other sources such as a database, other excel files, the web, etc. To do that there are various tools that you need a basic understanding. Even with a basic understanding you will surpass 80% of the people around you. This requires some new learning, but it doesn’t take much time. The best tools to interact with outside information is Power Query and SQL which is summarized below. All examples are included in the template below.
- Structured Query Language (SQL) communicates with databases. What’s important about this is that databases store all the data which is in table formats. Once you extract the tables you can analyze the data with pivot tables or any way you’d like. You’ll need to download SQL server to do any of this and learn some basic SQL statements. I started with limited knowledge and was able to accomplish everything needed to get done. A good book that helped me was SQL in 10 Minutes, but you don’t need to purchase anything to learn SQL and there are plenty of good YouTube videos (linked below). All you need is the basics to get most jobs done.
- Open Database Connectivity allows applications to interact with databases. This will need to be setup through your start menu to accomplish any of the above. Examples and screenshots will be in the template.
- Data Connections
- In Excel’s Data tab you can setup connections to your SQL statements. This is where the magic happens. Once it’s setup all you need to do is right click and refresh for your data to be up to date.
- Power Query
- This is an Add-In that comes free with Microsoft. In the most recent versions of Excel it comes without having to download it. This is a very powerful tool used for data discovering and manipulation. It performs the same similarly to SQL with the ability to reshape your information. The nice thing about Power Query is that you don’t need to learn coding. You just proceed with steps and the code is automatically written. It did seem like an entirely different system to me at first, so it did require some studying and of course YouTube watching. Here are some links below.
If you can get a good understanding of these four sections I guarantee you’ll be ahead of the pack and will impress people around you. Even if you’re at least aware of these people will know you’ve done your homework. Good luck and enjoy amazing everyone around you!