Excel on SharePoint

Upload this file to SharePoint and open it in the Excel Web App to create charts online.  Then, open the file in Excel and do more things with the resulting charts.

This is a quick intro to all kinds of formatting of chart axes.

The default units that Excel charts assume aren't always the best way to represent the data.  This lesson shows several ways you can customize the units display.

Prepare files that contain charts for use on SharePoint via the Excel Web App or the Excel Web Access Web Part.  Use the file's Browser View Options to specify what will appear from the file.

Insert an Excel Web Access Web Part on a site page or wiki page and configure it to show a specific Excel file, conforming to the file's Browser View Options setting.

This section is a quick intro to the various types of built-in conditional formatting in Excel.

It is possible to apply conditional formatting rules to an entire row based on the value of one of the cells in that row.  Download the attached files to work through this process on your own.  Watch the demo in the next video.

This is a continuation of the concept shown previously, this time applying conditional formatting to an entire row based on the values in two of the cells in that row.

This section provides a quick introduction to a number of Excel date functions.

This section continues using the same file to provide a quick introduction to a number of Excel's time functions.

This section shows how to "do math" on dates as well as how to "construct" dates from year, month, and day values.

This section teaches you how use the NETWORKDAYS formula to calculate the number of business days, including posted holidays, between two dates.

Build a "calculator" that takes any date from the past (the example here is a birthday), calculate this year's occurrence of that date, determine whether that date has already passed, and finally calculate the next occurrence of that anniversary (whether this year or next).

How to prepare the "Birthday Calculator" section to be used over the web via the Excel file's Browser View Options, setting a named cell as a parameter for user input.  Users can make use of the functionality of the calculator without having to open the file in Excel.

A simple introduction to the VLOOKUP formula.

The final variable in the VLOOKUP formula is a true or false "Approximate?" value.  Setting this to FALSE tells the formula to find an exact match (or return an error if it doesn't find a match). Setting this to TRUE allows the formula to find the nearest match in line.  We use this property to find data about values between two ranges.

List Data Validation is not supported in the browser (either the Excel Web App or the Excel Web Access Web Part).  It does provide a useful in-cell drop-down menu to present a list of values to look for.

Use named ranges from anywhere in a workbook in a VLOOKUP formula, without having to go find the referenced table or range of cells.

Create a "calculator" on one worksheet that pulls data about a specified account from a different worksheet (using named ranges).

Since list data validation is not supported in the browser, learn to use PivotTables and Data Slicers to narrow down the value in a single cell of the PivotTable as the selection to search on in the Account data VLOOKUP.

The finished workbook shows one worksheet with a series of VLOOKUP formulas, looking up account information on a sheet that is not shown.  The selection of account is driven by user interaction with data slicers connected to a PivotTable that is on a worksheet that is also not shown.

This is a dashboard the displays various charts driven by commonly reported data about a collection of projects. 

The Gantt Chart is not a built-in Excel chart.  Customize the data in the worksheet to change the layout of the existing Gantt Chart in the file, or work through the Word tutorial to learn how to make your own Gantt chart. 

A very common approach to dashboard development is to group together related components on one worksheet for logical analysis.  The internal hyperlinks give users an easy way to move from one worksheet or section to another without having to know where those physically reside in the workbook.

The "dynamic" nature of these data-driven dashboards is the ability to interact with the presentation to see different subsets of data to answer questions that "static" reports would not be able to answer.

Build a Wiki page that pulls in specific components of one or more Excel workbooks onto a single page, using the Named Item property of multiple EWAWPs.

The default default new document template in SharePoint document libraries is blank Word documents.  It is possible to create document libraries with different kinds of Office files as their new document templates.

A newly created document library has as its template a blank file, whether it's Word, Excel, or PowerPoint  In the library's Advanced Options, you can edit the blank template to add whatever elements you need to be part of the template.

It is also possible to upload your own custom template (which can be macro-enabled) to the library's hidden Forms folder, and then use the new file's URL as the library's new document template.

So far in this course, we have been connecting Excel Web Access Web Parts to specific Excel file's (and sometimes to show specific components).  Learn how to connect an EWAWP to a Document Library Web Part so that the EWAWP displays the selected file from the document library in the EWAWP (using the file's Browser View Options).