The Microsoft Office spreadsheet software MS Excel is widely used for a large range of applications, from keeping a list of items up to date to generating invoices or even playing video games.
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.
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.
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.
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.
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..
Even though I hated learning Excel, it's one of those necessary skillsets that we can apply to many facets of our work and daily life (budgeting, etc).
Here is a good tip for saving time with Excel formulas, which many people tend to struggle with. So what you want to do is use the tab key, which lets you auto-select the formulas. This way you don't need to waste time typing them out in full each time. As you start filling it in, the field will populate a list of suggestions from Excel. Then all you have to do is move your arrow key up or down to select your formula, hit enter and done.
If there is a problem with any of the formulas, you can quickly debug it by selecting the formula then hitting F2. Then you can cycle through parts of the formula by holding shift and pressing the left arrow. F9 will show the final calculation of the formula, after which you can hit Esc to keep the formula or enter to preserve the value.
Philip Weiss, Founder, PhilipWeiss.org
After realizing the 9 to 5 life wasn't for him, Philip quit his job in the aviation industry and decided to travel the world in his own terms. Currently, he is based between NYC and Cambodia, he is a full-time digital nomad and the founder of his travel blog, PhilipWeiss.org.