A world without computers. Seems as prehistoric as living without a written language, even if the rise of the machine is fairly modern.
Computers make our lives easier – mostly by providing needed information quickly and accurately.
Excel seems to make life easier too.
Would it surprise you to know that about 90% of businesses use spreadsheet applications for salary management? The most popular being Excel, according to a 2012 World at Work Consulting study.
About 80% of companies, big and small, use Excel for budgeting purposes.
Why such high percentages?
For salaries:
- 72% of organizations use a spreadsheet to administer pay, according to the same study.
- 62% use it to record salary range data.
- Many companies also use the program as relational databases—databases structured to recognize relations among the information stored in them. These are essential to quickly search and retrieve specific information, view the same data set in multiple ways, and reduce data errors and redundancy.
For budgeting:
- Excel, as a budgeting software, provides budget managers with a lot of control on the format of templates. The layout can be changed when the business environment changes. With a different system, budget managers have to give up the control to design a template that meets their exact business needs.
- Adding new worksheets can store and provide an audit trail for budgeting numbers.
- Anyone who owns a copy of Microsoft Office can be involved in the process. No need to buy an expensive, fancy system, either.
- Companies do not have to constantly upgrade their software so that it will work with the latest operating system.r.
Chances are, Excel is already being used at your workplace. Any computer you sit down at in the office is probably equipped with the program – thanks to the bundled package of Microsoft Office.
How do you use the program to your advantage, however?
Wouldn’t it be great to use Excel in an improved way?
Taking that initiative might just get you noticed by the right people at work.
Check to see if your type of work involves one of these tasks, then learn how Excel can be applied to them:
Administrative Functions
Excel administrative documents are created and stored as files where information can be entered, edited, formatted and saved electronically. Additional graphs, photographs, charts, drawings and tables can be inserted into an Excel document to further illustrate and enhance administrative documents.
Storing Data
Excel can be used to store and retrieve numerical data in a grid format of columns and rows. Excel is ideal for entering, calculating and analyzing company data such as sales figures, sales taxes, or commissions.
Excel offers tools for calculating trends, deciphering profits, creating numerical formats and visual data. The Excel program can turn a spreadsheet of numbers into a pie chart, bar diagram or a variety of other visual diagrams. Excel can also be used to store text-based data such as client lists, employee data, mailing lists or inventory spreadsheets.
Reports
You can use Excel and Word individually or in combination to create company reports. Word is ideal for presenting reports in written form by summarizing and formatting data using a table of contents, headings, font changes and pagination.
Excel can be used to create the supporting data for a written report which might include selecting a particular range of data by date, department or personnel.
For example, monthly sales reports can be created according to customer, manufacturer, sales person or product using Excel’s data sorting and selection capability which is based on previously defining data by attributes such as department name, manufacturer or acquisition date.
Examples of company reports might include financial summaries, sales tracking spreadsheets, compliance documents or personnel reports outlining vacation and sick days.
Research
Companies often investigate and research possible business or production models in “what-if” scenarios. These scenarios might illustrate comparisons among pricing structures, the cost of doing business in different markets or geographical locations or the cost of producing next year’s holiday products through different manufacturers. Excel can store and calculate the data as directed using existing or new data. The data and calculations can be stored in separate workbooks (a workbook is a collection of spreadsheets accessed through a single file with each page of the file being a separate spreadsheet added as a new tab in the workbook).
Quora users (it’s a forum to share questions and answers on really anything), sum it up well:
So are there any quick tips to using the program?
Of course! These tricks will help you incorporate Excel into more of your daily tasks.
Let’s start with VLOOKUP (the basic function that allows you to look up a value in a spreadsheet).
1. Look up specific data FAST
When you’re selecting an entire array, a VLOOKUP on a massive spreadsheet can take up a large chunk of time. The main reason? It can only search in one direction.
Here’s a better formula:
MATCH returns the index of the value within the selected array, while INDEX returns a value based on a column array and a row number.
2. Try PowerPivot
I’ll say it again, VLOOKUP can take forever. If you are a Professional Plus user (your business might own this) then you can use PowerPivot to quickly relate columns across tables and analyze them instantly.
Or you can use the new built-in data model in the latest versions of Excel. With this service, you can link multiple tables with each other and generate Pivot reports with only a few clicks.
Be warned, knowing this trick will cause envy when you’re finished your work and everyone else is still a slave to copy and paste in VLOOKUP.
3. IFNA() formula is a winner
If you’ve ever used Excel then you know the frustration of the #N/A value.
Where did you go wrong? What happened? How bad is your karma that the universe would do that to you?
Now, there is the IFNA() function ready to save you. It’s specificity is a time-saver, since it only considers the #N/A value.
IFNA(value, NAvalue)
Where value references the formula you’re checking for errors and NAvalue is the value you want returned if value returns #N/A.
If an argument references an empty cell, IFNA() treats it as an empty string (“”). If value is an array formula, IFNA() returns an array.
4. Check out Timelines
How big is your spreadsheet? It seems like everyone is trying to one up the other for biggest data accumulation, but does anyone ever really want to look at that much data?
More practical to show that you know how to access what you need, for when you need it.
This is where the timelines feature comes in, which lets you filter dates through a much faster method than by auto-select.
You can add timelines for any date column in a pivot table / pivot chart.
5. Distinct Count in Pivot Reports
Pivot reports now feature distinct counts of values in the newer Excel versions.
With no extra formulas, it’s a great time saver. Making those pivot reports (which are a quick way to see the big picture through all of your data) is also much easier with the new Recommended PivotTables function.
- select data and heading
- choose insert and Recommended PivotTables
- select the table you want and it will be drawn for you
Seriously, it’s that easy. Excel knows you have better things to do.
6. Flash Fill is your friend
Your new favorite shortcut is now CTRL+E
This feature uses predictive data entry to detect patterns and extract and enter data that follows a recognizable pattern. Yes, it’s a mind-reader.
Easily join names together, extract months, add initials, there are endless possibilities.
7. Goal Seek
Sometimes when doing what-if analysis, you have a particular outcome in mind, such as a target sales amount or growth percentage. The goal seek function can help find the input values needed to achieve the goal.
For example, your company might want to look at a certain month to see how many sales are needed to reach 100,000 dollars (or whatever goal it is you have in mind).
To use the Goal Seek feature located on the What-If Analysis button’s drop-down menu, you need to select the cell containing the formula that will return the result you’re seeking, then indicate the target value you want the formula to return as well as the location of the input value that Excel can change to reach this target.
Use this to figure out interest rates and other tricky formulas quickly.
Excel isn’t always easy to master. Need help? Want to start getting noticed at work? Find out the 6 key factors to consider when seeking a custom excel solution.
Conclusion:
Excel has practical implications across various business setups. Administrative functions, reports, research, or data that needs stored, are all tasks that Excel can help with.
With such high percentages (close to 80 and 90%) of businesses incorporating the program into their processes, it makes sense to expand both your use and knowledge of Excel.
Being able to work more efficiently, in a more organized way, or with more innovation are all leadership qualities that are sure to get you noticed at work.
Applying Excel to more of your daily tasks could be the ticket to getting ahead.
Remember these tips to get started:
= INDEX(column, MATCH(search value, search column,0)
Pivot Reports
= IFNA()
CTRL+E
Timelines
Goal Seek
Could your business benefit from using Excel in more diverse ways at the office? Let us know how. Comment below.
