8 uncommon tips for your excel files
Excel is a powerful tool for any company, but not everyone knows how to use it properly. That's why we offer Excel training classes so that you can learn more about this useful software and apply what your learned in the day-to-day work of yours! In our online sessions with instructor utilization examples from all over North America (and even outside), students are able gain skills quickly while also receiving personal one on ones throughout their journey. Lets go through some introductory tips below.
1. Removing Gridlines
Excel can look great. You just need to know some quick and easy tricks that aren't often taught in training or courses. Lets say you have a table that's displaying information but you aren't using borders you just use the standard excel grid, it will look a little crowded.
Here's how it looks when you remove the gridlines.
It changes everything and makes the sheet stop looking like a boring excel spreadsheet.
This look is easy to achieve. Visit the view tab and uncheck the gridlines check box or look at this quick video.
2. Make your ranges into tables
A lot of people believe that Excel is just a spreadsheet application but it has many more functions. For example, you can use it to create graphs or perform calculations with formulas and data sets. — An Excel training class is a great way to learn the basics of Microsoft Excel. It will help you understand the different features and functions which will make it easier for you to use this powerful tool in your day-to-day work.
We aren't called Table Originals for no reason. We love dataset tables. It's an amazing way to hold information. Excel tables are great because they host their own specific functions and can help with so many different needs. If you have data and you want Excel to be awesome, hit Ctrl-T and make that range a table. You will get a choice of some neat banded colors and filters. As well as a bunch of other sweet features like total row!
3. Use Slicers
Slicers are basically more user friendly filters. It let's inexperienced users manipulate a table. It introduces them to the powers of Excel. If you want to make your tables more fun to use you can use slicers to filter easily. Look at this example of me trying to find the average salary for Contract Managers in our table VS using the slicers to find that number.
You will see how the design and the appearance of Excel will change thanks to the use of tip #2 !
To add slicers, you will need to have a table or a pivot table in place and reach to the Insert Tab. Inside that tab you will find the Slicer button. After that you will have to choose which columns you would like a slicer for.
4. Know your date shortcuts
Entering dates in Excel can be a hell of a mess if you don't standardize your method. For most, dates will always be a mess. If you have 10s of users on a file, it's better split it in day, month and year columns. If you're on your own, you can use shortcuts. If you know how to enter today's date in excel standard format, your life will generally be better.
The shortcut is Ctrl-; , that means you have to hold "CTRL" and hit the semicolon button on the right of the L Key. This will automatically input today's date in a cell. It will hold the proper format and will work well through time. You can also hit Ctrl-Shift-; to get the time right now. This method is useful to make timesheets.
5. Learn Conditional formats
Everyone in Excel loves colors. They're a great way to make a boring document into a fun to read and great tool. The big issues with colors is that they aren't real data, they can't be easily connected to other tables. Yet we love them still.
I've seen tables made by different firms that work only with colors. They will say Green will be Go, Yellow means require Audit and Red means must train.
Why not Make Simple conditional formats like when GO is written cell becomes Green, RQ becomes Yellow and TR means to train and will become RED. The best part is if you make the font and the background the same color the words won't show and you will get the glorious color! Here is what it looks in action
Keep in mind conditional formats can do so many things. Someone even programmed the entire game of Asteroids in Excel only using conditional formats! Think of how powerful it needs to be to achieve such a thing
6. Use the theme colors
Each time you have to choose colors in Excel you are given the option to choose Theme Colors or Standard Colors. Theme Colors mean that you could edit all of them quickly in 1 swift change. Say you use the orange in Theme colors to make your table design. You could use all the sub tints of orange or make it lighter and darker based on the theme you're going for. You can change all the tints of orange you used simply by going in the Page Layout Section and changing the colors. It's such a great way to give each of your files a fresh touch simply by changing the color scheme.
When you choose colors be alert there's the option to never change, or being able to edit the color design of the document in 1 swift move!
7. Use Flash Fill
In the year 2013 Excel brought a simple AI into Excel to help you manipulate your columns as well as clean up. This Flash Fill AI is able to do some fancy things. Generally, Flash fill will require you to have columns of information. A great example is having a column of Names. They are together in 1 cell, like in this case.
Most of the time to get things like this done you have to either learn how the Text functions work in Excel like Mid, Left, Right or use Text to columns. In Google Sheets, there's a neat feature called Split.
Now it's easier than ever to get this done. All you need to do is type the split you want in the first cell and then jump under and hit the Flash Fill shortcut CTRL - E
Remember that this can do any type of splitting or combining. All you have to do is enter the expected result in the first line and have the rest empty. You can hit CTRL-E to make the flash fill happen.
This is useful to extract address parts, create new columns that are unique, split dates and split imported information from CSV systems that haven't been well cleaned. Flash fill is a welcome addition to excel and saves a lot of time when you have some complicated splits or combinations to make.
8. Use Shapes & Cell connections
Sometimes merging cells can be dangerous. This often involves you needing to add something to your design that doesn't fit the normal grid architecture. To solve this, you can go in the insert section and add shapes to your file to create some really cool looking things. One little known fact is that you can create simple =$A$1 links for your shapes to show dynamically the contents of A1 Cell. This is super useful as it lets Excel be even more customizable. These shapes are also in graphs and other neat little tools.
All you have to do is click the shape go in the formula bar and hit equal (=) and click a cell. Once that is done validate with Enter and you cell and shape will be linked.
WARNING: You can only have a link to a cell as a formula, if you try to make something else, it won't work.
The most important thing to know about Excel is that it's not only a spreadsheet application, but also an organizational tool. You can use it to store data, organize your work and collaborate with others on projects. A good example of this is when you are working on a project with other people and you need to share your workbook with them. All you need to do is export your file as a CSV (Comma Separated Values) file or an XML file which can be shared easily through email or by uploading it somewhere online. Excel training classes are usually offered by organizations that specialize in teaching Microsoft Office skills and they are usually free or very affordable. You can also find free tutorials on the internet that teach all sorts of tools but I hope you liked this one! 🤝🤗