Thursday, July 2nd, 2015 / by ExcelHelp
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?
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:
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.
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.
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.
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:
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.
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.
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.
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)
Could your business benefit from using Excel in more diverse ways at the office? Let us know how. Comment below.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.