What are your thoughts on Microsoft Excel? In most cases, people would say they love it, or they hate it, or they are too intimidated to delve into it.
Thanks to the influx of more user-friendly cross-platform applications for data storage, many would also argue that Excel has become far too dated for regular use. However, Excel continues to dominate the business world.
It's often used for complex analyses, in addition to forecasting models and storing vast amounts of data in a single file. And while there are plenty of applications designed to replace spreadsheets, they often fall short on various arenas, which leaves you with limited options. As simplistic as it may appear, Excel usually offers you more usability and data control.
With a little bit of practice, it's perfectly possible for most anyone to squeeze the most out of Excel. It's come a long way since the '80s. These five tips can be used personally and professionally, and some of them don't even call for endless rows of digits:
1. Create a custom calculator.
The capabilities of calculations in Excel go far beyond simply adding subtotals to view the grand total. If you find yourself running the same complex calculations over and over again, let Excel deal with them so you can toss your old calculator:
Open a new file, and label fields for what interests you. This can include rate, quarterly periods, present/future value, and payments.
Select the cell you want the result of each of the labeled fields to go to. Click "Insert" and select "Function" to open the "Insert Function" window. Then select "Financial" to view all the functions in the financial calculation.
Double click the labeled field of choice, which will open a function arguments window. Fill in the field numbers as how you labeled them. Click "OK" and you're done with the calculator for that label.
Continue with all other labels.
2. Make use of accounting functions.
Excel is fully equipped for loan calculators, financial reports, expense tracking, forecasts, and budget plans. Spare meeting with the accountant and view metrics like revenue, operating profit, interest, depreciation, net profit, and quarterly trends at a glance. Pivot tables can help you create dynamic summary reports from raw data very easily, all in a drag-and-drop interface:
If you're doing this on a new spreadsheet, click on cell 1A, and then click on the "Number" tab at the top of the page. Under "Format Cells," select the "Accounting" option. Unless you wish to make additional adjustments, select "OK." You can deselect showing the currency symbol if you wish at this point.
You can apply this format to a range of cells by selecting the range of cells with a format painter tool.
Built-in formulas that can be applied and tweaked to customize include cash flow and asset depreciation. After applying the formulas, continue creating other formulas that branch off into new column headings, such as date, balance, and amount.
3. Transform numbers into charts and graphs.
All it takes is a few clicks to transform rows and columns of numerical data into charts and graphs, which are far more visual and digestible. It's a major timesaver for data analysis:
Enter your data into the spreadsheet. For example, A1 could say "Date" and B1 could say "Number of Signups." A2 and B2 downwards would have the data entries as they correspond with one another.
When done, select the top left cell, and then while pressing "Shift," click on the bottom right cell. This will highlight all the data.
Click the "Insert" tab up top, and select "Chart" and "Recommended Charts."
Click a chart option, or click on "All Charts" for additional options.
4. Map out daily calendars and schedules.
You already have software for daily calendars and schedules. Sure. But why turn to many individual pieces of software when one can handle it all?
Use Excel to map out a content calendar for your website and blog. Use it to maintain a PTO schedule of all your employees. Color-coordinate for different categories, so you can get a quick grasp of areas that may need more focus. It'll help you monitor progress more efficiently:
Conduct a search on schedule templates. This varies greatly depending on which version of Excel you're using.
Preview the schedule templates, and download the most suitable one to open into a new worksheet.
Alter text/colors as needed and desired, and get right into inputting the data!
5. Fetch live data from the internet.
Excel can automatically update figures--stock prices, FX rates, results of sports games, flight data of airports, and any info in a shared database--from a live data source. It sure beats tedious manual entry on a daily basis.
Note that this functionality, which is called "Get & Transform/Power Query" isn't available in the 2007 version. Only 2010 and later:
If you're using 2010, download and install the Power Query Add-In. This is already built into 2013 and later.
Click "Power Query" (or "Data" > "New Query" > "From Other Sources" > "From Web")
In the "From Web" box, enter the URL. Provide user credential info if needed from the website itself. Click "OK."
Power Query will scan the webpage, and load the data in the "Navigator Pane" under the "Table View."
Select the table you want to connect to by clicking it from the list.
Click "Load," and the web data will be seen on your worksheet.