By Hee So
Online Career Tips, Special Contributor
Microsoft Excel is my best friend at work. I spend most of my day in it analyzing data, doing calculations, and creating crazy reports. When I first started using it, I mostly knew the basic calculations and formatting techniques. The more time I spend in it, the more I realize how helpful and powerful of a tool it is. There is so much you can do to help you save time and be more productive at work and I would like to share some of my tips and tricks with you.
#1: Opening Excel workbooks on multiple screens
Do you ever get frustrated tabbing between multiple Excel files to copy/paste numbers and to perform calculations? Did you know you can open spreadsheets in separate windows so you can look at two different spreadsheets at the same time? This is a huge lifesaver and can help you be more efficient while at work. I usually always have at least 5 to 10 spreadsheets open at any time during the day and love being able to see my files side by side to easily do formulas that require data from another workbook. What most of us do is when we already have an Excel file open, we just go to file, open, and then select the next spreadsheet we need. The trick is to actually open a new instance of Excel and then select and open your file. This will allow you to drag your workbooks from one screen to another and this way you can view different Excel files on multiple screens.
#2: Filter/Sort
If you are working with large spreadsheets that contain hundreds and thousands of rows of data, Excel’s filter and sort options will help you save time. You can apply filters to just look at specific rows of data from your file. Let’s say your file has 10 years’ worth of data but you only want to look at the numbers from the last 2 years. You simply apply a filter that will create a drop down for each of your column headings and you can select the appropriate dates from the dropdown. The sort option also comes in handy when you are doing data analysis in Excel. You can sort numbers from highest to lowest, list names in alphabetical order, and create custom lists. Sorting data helps you visualize data more easily to help you understand what you’re analyzing.
#3: Excel pivot tables
Pivot tables are the best way to look for trends and patterns in your data by being able to quickly take large amounts of data and summarizing it. I use pivot tables the most in Excel due to the large amount of data I analyze on a daily basis. I am easily able to subtotal all my numbers, create my own formulas and calculations with the data, and expand and collapse data into categories. You can also create charts using the pivot table data which is always very helpful when trying to visualize trends in the data.
#4: Customizable status bar
The status bar in Excel appears at the bottom of the window and is completely customizable. You most likely already have the zoom feature along with some auto calculations such as average, count, and sum. If you right click on the status bar, you can select from any of the options to enable. This is where you can add options like the minimum and maximum auto calculations and remove others that you may not want. I find the auto calculation options especially beneficial when I quickly need a sum or average of some selected data rather than having to create formulas that do the exact same thing. All you have to do is select the numbers from the rows or columns and the totals are already calculated and located on the status bar.
#5: Transpose
Have you ever created a spreadsheet with data in a certain way to realize that it was not the best way to arrange it and wish you had done it another way? Or maybe you received a spreadsheet from another colleague and you would like to reorganize the data into different rows or columns instead. The transpose feature in Excel allows you to take the existing data from your columns and rows and rearrange them in a different layout. This saves you time from having to copy and paste data one by one or re-keying all the numbers, especially if you have hundreds or thousands of rows of data.
There is so much you can do in Excel that can help you be more efficient while at work. I shared a few of my tips and tricks with you but we all know, the more you play in it, the more you learn! Happy data analyzing!
About the Author:
Hee So is a Senior Interactive Marketing Manager for American Public University System and has been with the company for 6 years. Hee is responsible for managing the University’s Google PPC search and display accounts and spends time focusing on reporting and analytics of paid search campaigns.
Comments are closed.