Office for iPad – a first look at Excel

Just installed Excel for iPad. I have an iPad 2 running iOS 7 and installing Excel from the App Store went quickly and smoothly.

Opening Excel on the iPad for the first time, I found the user interface clean and intuitive. After playing with it for a few minutes, here are my first impressions.

  • It’s easy to connect to your OneDrive, but you can also store files locally and use them off-line
  • The insert ribbon contains commands to insert tables, charts, pictures (from the iPad Camera Roll), shapes and text boxes. It even shows a button for Recommended Charts. The Charts button has every familiar Excel chart that you know from the full desktop version.
  • the Formulas ribbon contains buttons for the different formula categories and a little calculator icon on the right to recalculate the sheet.
  • When a table, chart, shape etc. is selected, a context sensitive ribbon tab appears with an impressive array of commands for the selected item, including styles, colors and much more.
  • Although data validation and conditional formatting cannot be defined on the iPad (as far as I can see), they work great if a file already has conditional formats or data validation set up.
  • If named ranges have been defined in a workbook, these work in Excel for iPad and they can be used to create new formulas on the iPad.
  • Excel for iPad supports freezing panes, either first row, first column or both.

Here’s a screenshot of a file I created on my computer and then emailed to myself. I started off with this:

Workbook prepared with Excel 2013 for Windows

Workbook prepared with Excel 2013 for Windows

I added some data, created a table from that data and created a chart from the table. Added a shape, moved the data validation cells to a different position, changed numbers in the conditionally formatted ranges and added a new formula using named ranges and the =Formulatext(C13) to show the formula I added.

Excel on iPad

Excel on iPad

Here is what it looks like with the data validation drop down opened. The screen is overlaid with grey and the data validation box really stands out.

Excel on iPad

Excel on iPad

VBA and external data sources are not supported in this release. When opening a file that contains VBA or links to external sources, a friendly message appears that informs the user, but after that the file will open fine.

Overall, I am very impressed with this Excel experience for iPad. You would not want to create complex models from scratch using just iPad, but it is now possible to take your Excel files with you, store them on your iPad and work on them off-line, doing more than just simple data edits.

Addendum Sunday 30th March 2014: This review is a very quick recap of how Excel for iPad deals with some of the most frequently used Excel functions. Bill Jelen, aka MrExcel has a review inspecting much more comprehensive scenarios here.

Share this:

Slides and files from MVP Com Camp – Power Map

Thanks to all the interested people who turned up at this fantastic event today in Auckland. It was a glorious day and I would not have blamed you if you had chosen the beach over a conference room.

I’ve posted my slide deck and all the files I demoed or used in videos in my OneDrive here: http://bit.ly/PZOrZm

Darrell took a few photos. I’ve pinched these:

 

 

Enjoy.

Share this:

SharePoint: Redirect if user clicked a button previously

I just came across this question in sharepoint.stackexchange.com. When a user visits a SharePoint site, they are presented with a splash screen and need to accept the policy before they can proceed. Upon subsequent visits, the splash screen does not show, because SharePoint will remember the  user.

PirateEric outlined a possible solution: Use a SharePoint list to save the user name when the button is clicked. When the page loads, look up the user in the list. If they already exist, redirect the page, if not, show the splash page with the button to accept the policy. If the policy changes and users need to be made aware of that, simply remove all items in the list that tracks the users. All this can be done with jQuery and web services.

That intrigued me and I had a go at actually building this, using Marc Anderson’s SPServices.

How to set it up

Create a SharePoint custom list with two fields, Title and UserName. The former is the out of the box field, the latter is a simple text field.

Create two pages, the Splash page with the button and the page that is the desired destination page for all visitors. In my sample these are called Splash.aspx and MainContent.aspx

On the Splash page the code that you can see below will be loaded before any other web part. If you use a Content Editor Web Part to load the code with a content link, make sure that it’s the first web part on the page.

In many cases, JavaScript and jQuery will be placed in the last web part of the page and run after the DOM has loaded. But in this case this would mean that the Splash page appears briefly, even if it is followed by a redirect to a different page.

The Splash page provides the policy (or terms and conditions) that the user must accept, and a link or a button that the user can click to accept. This link or button must then trigger a JavaScript function. That is very easy to do. I used a button and put the html straight into a CEWP like this:


<button onclick="PolicyButtonClick()" type="submit">
   I accept the policy
</button>

So the user clicks the button and the JavaScript function PolicyButtonClick() will run. This function can be found in the code below.

First, the jQuery library and the SPServices library are loaded. Then the user name of the current user is retrieved with the SPServices call using SPGetCurrentUser. It returns a text string in the format DOMAIN\Account.  Next, the SPServices call uses the GetListItem. In the call, a CAML query is constructed that will return only list items where the column UserName equals the current user.  The items returned by that query are then counted. Since the user account is a unique value, we can safely assume that the query will either return one result or no result at all.

If the query returned an item, this means that the user has previously accepted the policy and the script will redirect to the MainContent.aspx page.  If the query did not return anything, the current page will continue to be displayed.

When the user clicks the button to accept the policy,  the user name is written into a variable. Then the SPServices operation to UpdateListItem is called and will create a new item in the list “PolicyAccepted”, storing the previously established account name in the column UserName. Then the MainContent.aspx page is loaded.

The next time the user opens the Splash page, their account name will be found in the PolicyAccepted list and they will not see the Splash page again, unless the entry in the list is deleted.

Here is the complete script:


<script language="javascript" type="text/javascript" src="/path/jquery-1.10.2.min.js"></script>
<script language="javascript" type="text/javascript" src="/path/jquery.SPServices-2013.02a.min.js"></script>
<script type="text/javascript">
// start the code even before the DOM is loaded, so not waiting for document ready
//$(document).ready( function() {
// get the user name
 var userName= getUserName();
// find the user name in the list
 var userAccepted = matchUserName(userName);
 if (userAccepted == 1 )
 {
 // redirecting page
 window.location.replace("http://path/Documents/MainContent.aspx");
 }
//});

function getUserName() {
 var thisUserAccount= $().SPServices.SPGetCurrentUser({
 fieldName: "Name",
 debug: false
 });
 return(thisUserAccount);
}

function createNewItem(theTitle, theUser) {
 $().SPServices({
 operation: "UpdateListItems",
 async: false,
 batchCmd: "New",
 listName: "PolicyAccepted",
 valuepairs: [["Title", theTitle], ["UserName", theUser]],
 completefunc: function(xData, Status) {
 }
 });
}

function matchUserName(userName) {
 var queryText = "<Query><Where><Eq><FieldRef Name='UserName'/><Value Type='Text'>" + userName + "</Value></Eq></Where></Query>";
 $().SPServices({
 operation: "GetListItems",
 listName: "PolicyAccepted",
 async: false,
 CAMLQuery: queryText,
 completefunc: function (xData, status) {
 itemCount = $(xData.responseXML.xml).find("rs\\:data, data").attr("ItemCount");
 }
 });
 return(itemCount);
}

function PolicyButtonClick() {
 var userName= getUserName();
 var theTitle= "Accepted";
 createNewItem(theTitle, userName);
 window.location.href = "http://path/Documents/MainContent.aspx";
}

</script>

Enjoy.

Share this:

List all files in folder to Excel

I’m currently working on a project where I need to analyse documents, so they can be properly classified and uploaded to a SharePoint based document management system. Looking at the folder structure with Windows Explorer does not really help much. I need a list of files in their respective locations, so I can sort and filter, pivot and drill down.

There are some VBA code snippets out there which create reports of files in folders and sub folders in Excel, but none of them really do what I need, so I listed out my requirements and re-wrote some code originally posted by Leith Ross at Excelforum.com.

My requirements:

  • Specify a starting folder
  • Have all files and all sub folders listed out, or just the starting folder
  • Show starting folder in one column and the sub folders in separate columns each, so I can create pivot tables, sort and filter on the folder location
  • Show file name, file extension, file size, and core dates in separate columns, so the results can be sorted and filtered
  • Highlight large files with conditional formatting in three colours for > 2MB (yellow), >5MB (orange) and >10MB (red)
  • Keep a column with the original sort order (=location of the files)
  • Enable unlimited levels of nesting
  • Create the report in a new file, so I don’t have to clean up the original

I created an Excel table with these columns:

  • Order
  • File name
  • Extension 
  • Created
  • Modified
  • Size
  • Size MB
  • Path
  • Level1
  • Level2
  • … through to Level20

The table has one row of dummy data in which conditional formatting and one formula for the conversion from Byte to MB is defined. (Yes, I know I could do the conversion with VBA, as well).

Another sheet contains the user interface. It has just one big button to start the macro, which then

  • prompts the user for a starting location, which must start with a drive letter in the format “Z:\” and can contain a longer path.
  • checks that the second character of the location is a colon. If not, it cannot be a valid drive location.
  • prompts the user whether or not to include sub folders (just in case you only want to list out one folder)
  • goes off to work and gathers all the information, writes it into the table, splitting out the data into the columns
  • provides a status bar message with the folder that is currently in progress (the macro can run for quite a long time, depending on the number of files returned)
  • copies the report to a new workbook and removes the placeholder row in the table
  • closes the macro enabled file without saving, so nothing needs to be cleaned up.
If a folder has more than 20 nested sub folders, the macro will still work and write the folder names into the next available column, but the header of the table will have to be added manually.

The file can be downloaded here: CreateFileReport.xlsm

If you want to make changes to the original, make sure to save the workbook before you run the macro, or edit the code and comment out the row that closes the original workbook without saving.

Enjoy.

 

 

 

 

Share this:

Control-Shift-Enter — Win your copy

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Mike Girvin’s new book about array formulas called “Ctrl+Shift+Enter” is something you won’t want to miss if you are proficient with Excel formulas and want to get to the next step. Amazon introduces it like this:

Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions.

You can win one of four free e-book copies of the book if you enter the contest at Debra Dalgliesh’s site here.

Hurry. The competition closes on August 7th, 2013, at 12 noon Eastern Daylight Time.

Share this:

Percentage charts with conditional formatting

Last week I saw some amazing Excel spreadsheets while I was attending the MVP Summit in Seattle and I thought I’d share some ideas. So here are step by step instructions on how to create a percentage chart with just the Excel grid and a sprinkle of conditional formatting. The goal we’re after looks like this:

We’re looking at a grid of ten by ten cells that form a square (roughly). Each cell in this grid represents 1% of the whole. The number below the grid is represented visually by an according number of filled squares (or cells).

Here is how to do it:

Build the grid

First we need the numbers from 0.01 to 1.00 in steps of 0.01 in a grid of 10 by ten cells. If you want, you can type these 100 numbers in manually, but there’s a faster way if you use the fill handle (the little dark square at the lower right corner of the active cell). We only need to type in four numbers. Let’s start in cell C4 and enter 0.91, then manually enter 0.92 in D4. Cells C5 and D5 will get the numbers 0.81 and 0.82 respectively.

Select all four cells, then grab the lower right corner of cell D5 and drag it all the way across to cell L5.

Release the mouse button and you will see the numbers filled in and incremented accordingly:

Leave the cells selected, grab the fill handle in L5 and drag it down to row 13:

Release the mouse button and your 10 x 10 grid is filled with the correct numbers.

That was a bit faster than typing them all in, right?

Prepare the Total cell

Leave one blank row below the grid, then select cells C15 to L16 and use the command Merge cells from the Alignment group of the Home ribbon.

After merging the cells, let’s enter a number, say 38% and do some number formatting. I’d like the cell to show the text “Total:” as well as the percentage number. Click the lower right corner of the Number group on the Home ribbon to open the Format Cell dialog. In the left hand pane select Custom at the bottom of the list. In the Type field enter the text
Total\: 0%

Note: The “\” in front of the “:” sign is needed make sure it appears correctly. If we did not add this, Excel would get confused, since the “:” has a special use in time formats

Hit OK and see how the cell shows text and number, even though the formula bar clearly shows that the cell only contains a number. Crank up the font size a bit. In this example I have used 28 pt Century Gothic.

Format the grid

Now we need to apply some TLC to the grid. Let’s hide the spreadsheet gridlines. On the View ribbon, untick the Gridlines box and the spreadsheet appears in uniform white.

The 10 x 10 grid should have light grey borders, so select the 100 cells …

… and open the Format Cells dialog on the Border tab. Select a light grey colour from the colour drop-down and after that click the Inside and Outline boxes.

The cells now feature a faint grey grid, but the numbers are still showing. Here’s how to hide them for good: Keep the 100 cells selected, open the Format Cells dialog again and activate the Number tab. Choose the Custom option from the number categories and in the Type field enter three semicolons

;;;

A number format in Excel consists of four parts to define positive, negative, zero and text values. The different parts are separated by semicolons. If the format definitions between the semicolons are missing, then the number will not be shown at all.

Now the grid is empty and we’ll set row height and column width to make it as close to square as possible:

Select columns C to L and use the Format drop-down on the Home ribbon, where you will find the Width command. Then select rows 4 to 13 and use the same dropdown to select the Height.

Apply Conditional Formatting

The last step is the conditional formatting of the 100 cells. Again, this can be done in one go. Select the grid, then click the Conditional Formatting drop-down on the Home ribbon and select New Rule.

In the next dialog click Use a formula to determine which cells to format. In the formula box enter this formula:

=C4<=$C$15

Note the position of the $ signs. Only the $C$15 should have them. Don’t put them anywhere near C4.

Click the Format button and select a darker grey as a fill colour.

Hit OK on all open dialogs.

That’s it. You’re done.

Change the number in the merged cell below the table and see the grid update with more or less grey blocks.

Of course, you can use other colours. Look at my Fruit Machine below. Click the blue cell and hit the Delete key to make the numbers change. Can you get all of them to display the same number?

Depending on your browser and/or screen resolution, you may see some of the grid lines wider than others. To download the workbook just click on the Excel icon in the dark bar below the spreadsheet.

Enjoy!

cheers, teylyn

Share this:

Where did the MSDN SharePoint 2010 files go? All replaced with SharePoint 2013?

A few months ago I clicked a bookmarked link to an MSDN article about setting up the development environment for SharePoint 2010. Here is the link:

http://msdn.microsoft.com/en-us/library/ee554869.aspx

To my amazement, I found that the article now contains content about setting up the development environment for SharePoint 2013. I tried to navigate to the same tree node in the SharePoint 2010 section, but it does not exist. That change must have happened not too long before, though, because I still found the 2010 article in the Google cache and quickly saved it to OneNote. Phew.

But of course, that cannot work forever. And other saved links, and lots and lots of links on web sites that refer to SharePoint 2010 MSDN pages now show 2013 version content. And no way of selecting a drop-down to show the content for another version.

When I looked through some of the Excel help, I noticed that every 2013 help page I visited had a drop-down to select the version, for example http://msdn.microsoft.com/en-us/library/ff194068.aspx . When the drop-down is selected, the URL changes, but not by much. The string “(v=office.14)” is added before the .aspx to produce http://msdn.microsoft.com/en-us/library/ff194068(v=office.14).aspx , which will show the 2010 version of the page.

The drop-down is not there on the SharePoint MSDN pages, but the URL change works all the same. So, if your saved MSDN bookmarks or links to MSDN about SharePoint 2010 now show SharePoint 2013 content, just edit the URL and insert

(v=office.14)

before the .aspx

For the article about setting up the development environment, I changed the URL from

http://msdn.microsoft.com/en-us/library/ee554869.aspx

to read

http://msdn.microsoft.com/en-us/library/ee554869(v=office.14).aspx

and hey, presto, there it was in all its glory and original content.

Now I can delete my OneNote page with the copy. (Or maybe I’ll keep it. Just in case…)

Cheers, teylyn

Share this:

Combine a stock chart with a line chart

Stock charts can take some time to get used to and are among the more unwieldy charts in the Excel chart palette. They get even more challenging if you want to extend the out of the box chart. Recently, a user asked how to combine a stock chart with a line or XY scatter chart. Trying this out I created a stock chart, then copied the data for an additional series and pasted it into the chart with Paste Special > New Series. I then tried to change the chart type of the selected series to an XY chart, but found that a stock chart cannot be combined with another chart. Excel presents an error information message, saying that “Some chart types cannot be combined with other chart types. Select a different chart type”.

Playing around a bit more, though I found that it is actually possible to just select the newly created data series and format it.

After adding the new “data” series to the chart, select the chart. Then click the “Layout” tab in the Chart Tools ribbon.

On the left hand side of that ribbon, select the newly inserted series in the drop-down of chart elements:

Then click “Format selection” just below the drop-down:

In the format dialog, the series can be given a line color and markers, just like an XY or line chart would take, so there’s really no reason to assign a different chart type to the series. The result looks like this:

Here’s the workbook. Tried and tested in Excel versions 2007, 2010 and 2013. You can change the values in the cells below and see the chart update right here and now. Depending on the speed of your interweb connection, you many need to take a breath or two, before the chart updates. :-) . You can download a copy of the workbook by clicking the green Excel icon in the black bar below the workbook.

cheers, teylyn

Share this:

Showcase high and low values in Excel charts – Part 4

Introduction

In the last part of this mini-series I’m showing how to draw attention to high and low values in Excel charts by using background fill. The first part of this mini-series showed how to present minimum and maximum values of a chart by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart. In Part 3, an image was used as a background fill on a stacked series.

Background fill

So let’s look at how to use a background fill for the highest and lowest data points. This can be achieved by plotting a column chart on the secondary axis. First, we need to prepare the data.

The minimum values are identified with

=IF(B2=MIN(Value),1,0)

The formula for the maximum values is

=IF(B2=MAX(Value),1,0)

The prepared data table looks like this:

Plot all three data columns as a clustered column chart. Then select the value series and send it to the secondary axis. Format the “min” and “max” series to have 100% overlap and a very small gap, like 10%.

Format the primary Y axis to a maximum of 1.

The tricky thing with this variant is to have the secondary Y axis appear on the left. To do this, follow these steps:

  • On the Layout tab, select the Axes drop-down and show the secondary X axis. Now all the columns will be hanging upside down.
  • Format the secondary X axis and let the vertical axis cross at category number 1
  • Select the primary Y axis and format it to have no tick marks and no labels
  • Format the secondary Y axis (which is now positioned at the left of the chart), and format it to let the Horizontal axis cross at axis value 0 (zero), which will put the columns on the base line again.
  • Delete the primary X axis and fine tune the chart formatting as desired.

The result will look something like this:

Take your pick

I hope you have found this mini-series useful and will be able to apply the techniques to your charts.

The attached Excel workbook illustrates all charts above on separate sheets.

Enjoy.

Conditional Chart Highlights Part 4

Share this:

Showcase high and low values in Excel charts – Part 3

Introduction

In this mini-series I’m showing how to draw attention to high and low values in Excel charts. In the first part of this mini-series, minimum and maximum values of a chart were showcased by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart.

This post shows a different approach.

Stacked columns with image fill

If you would like to retain the original colour of the chart columns, you can use another technique to highlight the maximum and minimum value: an image, for example an arrow, can sit on top of the maximum or minimum value bar and draw the reader’s eye to it. You will need two images, like coloured arrows or happy/unhappy faces or similar in a folder on your hard drive.

First we need to prepare the data table. The original data table looks like this:

Now we add two columns to the source table. The minimum value can be calculated with this formula:

=IF(B2=MIN(Value),MAX(Value)*0.1,0)

To calculate the maximum, adjust the formula to this:

=IF(B2=MAX(Value),MAX(Value)*0.1,0)

Why multiply by 0.1?

The objective is to have a data point on top of the high and low values that has a consistent size. It can then be formatted with an image fill and that image always remains the same size, regardless of the value or height of the data value it is stacked on. Of course, we could use a constant value, like 10 or 150, but if the values of the underlying data have a very wide range, a constant may not be the best choice. A value of 150 stacked on a column that shows 3 Million will not be very visible. Instead, this formula shows a percentage of the maximum value in the chart. Adjust it to your needs.

This formula returns a value of 10 percent of the maximum number in the “value” series, and that will be a consistent size across the chart. Applied to our sample data, the source data now looks like this:

Select this data table and create a stacked column chart. After some minor tweaking (delete the legend, remove the grid lines, decrease the gap width) the result will look similar to this:

Now we can replace the red and green fill for the stacked data points with an image. Select the data point for the maximum value, which will be stacked on one data column. Open the formatting dialog and on the “Fill” panel tick the “Picture or texture fill” option. Browse to the image on your hard drive and select it.

Repeat the formatting for the minimum data series.  In this example, I have used a red arrow for the minimum and a green arrow for the maximum data value. The result looks like this:

A sample file with this chart technique is attached.

Conditional Chart Highlights Part 3.xlsx

In the next and final part of the series we’ll explore background fills.

Cheers, teylyn

Share this: