However, in daily business use, lot of basic users are not even aware of very useful Excel productivity tip that could save them lot of time and energy, for example to count number of occurences in a list of thousands of entries, or for more simple operations such as find the position of a character in a string and other operations.With only a little bit of training, it is even possible to use in daily business usage very useful functionalities with the Excel wildcards, a set of characters that can replace any other one in a formula. Used in an advanced vlookup in Excel they can even become very powerful.
We asked the community of experts for their best tips on MS Excel usage, and here are their answers:
Are you using MS Excel regularly? What is the one single tip that helps your daily productivity at work and that you could share?
One Excel productivity tip is backing up your excel documents on the cloud.I’ve worked for weeks and years on specific documents and expected them to always be there, but then had situations where my computer broke down due to a water spillage issue or other failures, and lost my excel sheets. Now I know to always back them up so I’ll have them even if something unexpected happens, which is why I keep a cloud-based backup on hand at all times.
Stacy Caprio, Founder, Growth Marketing
You increase your speed 3–5X when you get rid of the mouse. The best way to start learning this is to:
- 1.Hit the ALT key – you’ll see letters and number shows up all over the menu, that’s how you navigate from now on
- 2. Print out a page of Keyboard Shortcuts (search keyboard shortcuts on google images) and tape it to your computer
Getting comfortable using Excel without a mouse will take about 2–3 weeks.
The keys to being efficient are:
- Speed -> go mouse-free
- Automation -> learn how to write great formulas and format spreadsheets intuitively
If I was to rank an additional 3, here’s how I’d prioritize them:
- 1. Formatting & basic formulas (prep awesome looking reports and automate tasks)
- 2. Data analysis – filtering, sorting and analyzing data
- 3. Data visualization – tables, charts, and dual-axis charts
Kenny Trinh, Managing Editor of Netbooknews
Absolute References saved my life, I’m not too proud to say it. The heavens opened up for me when I learned to use the ‘$’ to create absolute references to other fields. For the uninitiated, the ‘$’ in front of letter fixes the column and the ‘$’ in front of the number fixes the row. From there, the references can be as stable as you need. It really made my life much easier when doing large dataset analysis quickly.
David Johnson, Chief Technology Officer, Mulytic Labs
David Johnson, MBA, BA Computer Science, has worked in the tech industry for 20 years leading global teams and technology initiatives in US, Europe, and APAC for companies like IBM and Sungard. He is currently Chief Technology Officer at Mulytic Labs, while also performing data and technology management consulting at Clear Blue Data.
Here is my number one Excel productivity tip: Master the use of flash fill. Flash fill is a very powerful function that is handy when it comes to processing large amounts of data. Instead of perusing the data one by one, you can actually ‘teach’ flash fill what you need. Type in what you need in the cell beside the raw data a few times and then try if the flash fill function learns it. Sometimes you would need to type more in order for Excel to recognize the pattern, but once it does, it does the work for you.
In order to do this, click the cell where you need the data to be. And then find the flash fill button. You can see it on the Home tab, in the Editing pane which can be found at the right most part of the Excel window. Find the Fill button under the AutoSum and then click on the dropdown. Finally, click on Flash Fill and watch the magic. Nothing can be more productive than being able to provide data from your worksheet without doing the manual work. Norhanie Pangulima, Outreach Consultant, Centriq
I am Outreach Consultant at Centriq – the home management app which has been featured/cited in outlets like the NYT, This Old House, Real Estate Daily, Brit + Co, and others. At Centriq, I work with our content team on creating SEO-optimized content about home maintenance, home décor, home safety, and more.
Have you read?
I’ve used MS Excel when documenting important business information and one trick that I live by is making sure that I keep myself updated with the latest updates and shortcuts as keyboard and formula shortcuts which have not only sped up my work but has also encouraged me to keep learning. This practice has developed a ‘constantly learning’ mindset which has made me more motivated to grow and be productive.
Mike Richards, Golf Enthusiast and Founder at Golf Einstein
My name is Mike and I’m the golfing enthusiast and founder of Golf Einstein, a blog where I share my input on everything golf-related!
My best tip, however, is pretty simple: *Use validation data!* I know, I know, you should be beyond that, right? I mean surely you can make sure you’re spelling the names correctly, right? Listen, it only takes one time of spending hours trying to figure out why your nested sheets of calculations are suddenly broken and then realizing it’s because of the fact that you input invalid data for you to see the value of data validation.
Shayne Sherman, CEO of TechLoris
I've been using Excel for years. In some of my previous positions, there was a joke that any task I was given (including ordering lunch or making travel plans) I could perform better with Excel. Honestly, I probably could have.
Using Excel Macros to automate time-intensive and repetitive tasks has been a real game-changer, and perhaps the single thing that has been most efficient in terms of how Excel has streamlined my work.
Merging data from other sources with Excel workbooks is something else that helps, and there is very little that can’t be integrated into an Excel workflow to merge it. Once more, you can import these other elements automatically to enable later data transformation and analysis.
Just raising a query to get stats before creating a report provides in-depth insights that can inform analytics and decision making, and enables you to validate and cross-check all of your various other forms of content too.
Polly Kay, Senior Marketing Manager at English Blinds
Polly has over a decade of experience as a digital marketing consultant and senior marketing manager, serving a diverse range of clients ranging from SMEs to large international corporations and household names.
When I was first learning Excel, I remember that the HLOOKUP and VLOOKUP really unlocked a lot of amazing things I never knew you could do with Excel.
As a CEO, I still use those functions to build really simple dashboards that will pull in data from other tabs within a spreadsheet, which is powerful for data we update on a regular basis.
Reuben Yonatan, Founder and CEO of GetVoIP
Being a Project Manager, I have to use excel daily. I have to keep track of a ton of data and analyze it daily to maximize the company’s value. I have found Excel as the most powerful tool in this regard.
I have to prepare reports daily. Many times I have to refer to old spreadsheets. It becomes a daunting task to go through all the data. So, I create graphs and charts of needed data, which I can export in other formats as well like PDF. It helps me to note down the findings in a glance. It gives me a quick overview of how numbers on the spreadsheets are related to each other. This feature becomes handier in the urgent tasks where I have less time to prepare reports. However, the precondition is you have all data as per Excel guidelines.
Gintaras Steponkus, Marketing Manager at Solid Guides
My name is Gintaras Steponkus and I am a Social Media Marketer at Solidguides. Solid Guides is a project created by a team of enthusiasts dedicated to research and writing reviews. We are very passionate about the Internet, and we know that it’s a huge place loaded with plenty of fake and unnecessary information. We created Solid Guides to make the Internet a safer place for shoppers, and because we love to research and inform.
I am using MS Excel regularly for analysis, reporting and storing data.. I have to make a report every week of all content, ads and traffic analytics to the investors and other shareholders of my news website.
Excel plumbing can increase the productivity level by many folds. Excel plumbing follows two strategies that can increase your productivity, one is the basic excel strategy and other is the advanced plumbing strategy. In productive strategy, most of the time is utilized in finding new insights and continuously improving the reports. It is a repeatable procedure in which you are improving the data of reports instead of maintaining it.
In case of advanced strategy, you use power pivot to increase your productivity. Here, you get multiple advantages. Firstly, the capacity to handle files increases. Billion of values per column and million tables per workbook can be stored. Secondly, by using relational procedure in Power Pivot Model, workbooks get linked with each other and a link is established between the tables.
Thirdly, your huge calculations are easily done through DAX functions. Hence, each and every function associated with power pivot is making the exploring, massive analyzing and reporting task easier.
Aqsa Tabassam, Editor-in-chief InsideTechWorld.com
My name is Aqsa Tabassam, a businesswoman, Co-Founder and Editor-in-chief of InsideTechWorld. InsideTechWorld aims to deliver the latest technology & startup news, media events, and groundbreaking happenings to its reader's all across the world..