A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
There is always a set of problems we repeatedly run into as data analysts. We promise ourselves to fix them one day but that day never comes. Well, today might just be your lucky day. Oz is here to share with us some tips and tricks that has helped him smooth out his workflow.
It is very tempting to clean/modify the data right where it resides. But often we realize we need to roll back to the original data and start all over again. So, if you overwrote the only copy of the data you had, be prepared to look stupid in front of your colleagues or client.
It’s always better to keep the raw data in a separate sheet and never touch it. Color the tab red as a precaution.
For the nocturnal analysts amongst us, there is a skin in Excel that can prove useful while working in dim lighting. Go to File > Options > General > Office Theme. Now select dark gray or black. Cool, right? Notice how the red colored raw data tab just pops out in these settings.
Now, what about the cells? Well, color them gray as well! If that seems like a hassle, especially when you’d want to revert to white later, try to explore settings of Home > Cell Styles > Normal. You’d find a good, easy solution there.
Go to View > New Window. Now you have two views of the same file, simple as that. It is very helpful especially when you have two screens. And since the two views are of the same file, all changes get updated in real-time in both views simultaneously.
That’s right, the secret to Oz’s productivity is his ambidexterity with a computer mouse. Hence, he uses two. Left-hand, right-hand, crap data can’t win!
Instead of zooming in and out, another way to increase view of your sheets is to hide the Ribbon. Just double-click on the name of the active tab and it will be hidden away. Double-click on it again to unhide it.
Note: the Ribbon is still functional and can be used while its hidden.
We tend to use some tricks / techniques repeatedly. It is always a better idea to automate them end-to-end. In achieving this, a repository of rich datasets can be quite helpful. For example, a list of names or drinks of various sorts. You may use CHOOSE, RAND and RANDBETWEEN functions combined to sample from them.
These random datasets are also good to showcase products you’ve developed in Excel, such as a cool analytics tool, to persuade your potential clients into buying your services.
Let us know which of these tips you found helpful in the comments section below. And do not forget to share this page with your friends and colleagues.
And while you are at it… Check out all the great Excel videos on Oz’s Youtube channel. He’s on Fire.
Excel Data Visualization: Presidential Approval Ratings with Slicers & Power Query – Chart Tricks
Excel Tutorial Concat Function in Excel with TEXTJOIN – Excel.TV Episode: 53
52: How to Create Running Totals & Rankings in Microsoft Excel Power Query
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide