Microsoft is one of the most powerful tools offered within Microsoft's suite of office tools.  And there is much more to this versatile application than what meets the eye.  Most users who work with Excel to create spreadsheets may only be using about 20% of what this powerful application has to offer.  In conjunction with Microsoft's online office offering 'Office 365', users can even edit their Excel spreadsheets from online and access them from anywhere they can connect to the internet.

Excel is capable of being used for complex analyses, as well as creating forecasting models and storing vast amounts of data in a single file.  On the face, Excel may seem simplistic, but it offers users more usability and data control under the hood.

With a little practice, it's possible for anybody to get the most out of Excel and take advantage of all it has to offer.  The following tips can help you get started to really take advantage of what Microsoft Excel has to offer.

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 time saver 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 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.

Working with Natural Networks managed IT services means that we work with you to find solutions to your IT needs when you need them.  We offer solutions that can help you and your organization thrive with software solutions like those listed above, and find new ways to implement and use IT to further your business goals.  If you want to learn more, contact us today!