Recently someone asked about some Excel hacks and tricks on Quora. These are some of tips I quickly came up with
Note: Here I will not teach ‘technically-how’ to implement these tricks in Excel. I will explain them conceptually and you can learn the technical stuff on your own. Who knows you may discover a couple new tricks while playing around.
1. Keep it Dynamic
Nothing is sexier than keeping things dynamic in Excel. You provide some input and the output changes based on your input. A chart, a data-set, a predictive model, a dashboard, a table, a linked-image, a look-up directory – almost anything could be designed to be dynamic in Excel. Here is an example.
My goal is to present a chart for multiple locations and I have three different locations. I can do something like this and produce three different charts for each location:
2. Use RANDBETWEEN to Simulate Something Quickly
If the Excel if really at core of your heart then you would require to do a lot of quick things that you will discard later.
Like I needed to create this chart above 🙂 I was really not worried about the data. I simply simulated it using RANDBETWEEN function and some 7th grade maths.
3. Use Font in Decimal Size
I think Calibri 11 is too cluttered and 10 too small. Therefore, the font I have used in table in the above picture is Calibri 10.5 and I quite love it. And some of my peers too.
Just go to the font size box and type any decimal number and hit return key. May be you also will find your favorite font for next 10 years.
4. Custom Format Things
Month Format in Table – Jan-14, Feb-14, Mar-14, Apr-14…
Month Format in Chart – J, F, M, A…
That is the power of Ctrl+1 and format the text in custom format. Here the one I have used is mmmmm to convert any month to the first letter of that month
5. Tweak Around Format Series Option of a Chart
Creating a chart is far beyond than actually presenting a nice beautiful data visualization. You will definitely experience this if you are using any Excel version lower than 2013.
This is what you get by default in Excel 2007
6. Use Color Formatting to Make It Visually Interesting
Do you see that table above I shared with you. Its alternative rows are colors in light shades contrasting with dark font colors. All text aligned left with 1 indent and all numbers aligned right, also with 1 indent. Remember – that 1 indent is very important for a clean look of table when it has multiple columns.
P.S. Border of table should be light and a shade darker than cell fill color.
7. And the Final and Most Important Tip
This is a little irreverent but hope you will tolerate me with this one 🙂
If you really want to become a data analyst regardless whether you store your data in Excel or Hadoop, you need to be skeptical. Do not just get the data and start running your learned tricks and tips on it straight forward. Always ask questions like
- Why I should trust this data
- My null hypothesis about this data is that this data is wrong, and now I will try to test if my null hypothesis can be rejected
- What are some interesting patterns in data (Oh ! Sales of umbrella goes high each July)
- Why are those interesting patterns (Because it rains in July)
If you also have some tips and tricks to share, please comment below 🙂