I talk a lot about Excel automation. Automating with Excel helps us be more productive and efficient in our jobs. Automating tedious tasks using advanced Excel features like Tables, Pivot Tables, Power Query, VBA or macros. The benefits are many – less stress, more time and better mindset for your private live and the more important things at work are obvious. Something similar to automating with similar benefits is easier accessibility to data. So Microsoft now has another ‘Power’ product called PowerApps (let’s hope for their sake the word ‘power’ never becomes passe`) which let’s you build apps that use your data including your Excel Tables to help you access and update data on the move.
Anyway, I had a crack at my first PowerApp this week just to see what it was all about. This is a new product from Microsoft for getting data mobile data from various sources like Onedrive, Sharepoint or Dropbox. Yes there is already a free Excel version for ipad or ipad but I am an Excel nerd and even I haven’t used that more than once. It’s nice to see the icon on my ipad but personally I think the concept of a precise grid with powerful features just doesn’t suit mobile devices. Perhaps I am ‘old school’ but serious spreadsheeting is for a desk with a couple of screens, scribbled notes, print outs strewn about and used coffee cups all over the place. All we Excel nerds have made Excel do things below it’s pay grade like being a To Do list or say a Shopping List, so I like the idea of not doing this anymore and using PowerApps to do specific tasks that suit a mobile device based on Excel data from a spreadsheet doing serious stuff in the background in the cloud. So I think Microsoft is on to something with this. In the past they were slow to get into mobile computing and also never rushed into the personal productivity space either until recently.
So I just made up a Shopping list in Excel in two columns with Isles and Items. It has a browse, detail and edit screen by default. It ended up being surprisingly useful little personal App in my local Coles shopping centre allowing you to filter by the aisle you are in in the search bar and tick of the usual items you buy.
There is not a lot of help out there for this application (a few Youtube videos will probably be needed to get you started) so for me it was a case of a lot of trial and error. You can start with lots of existing templates on offer. Or you can start with your data and let the app build an initial app. So that’s what I did I started with an Excel spreadsheet with my simple data in a table with three columns for Item, Aisle and Notes . I then went into PowerApps and created the app from data option navigating to the saved Excel file and the PowerApps then builds the initial app. I wanted two columns and a checkbox for each item and items greying out with strikethrough when the checkbox was ticked. Not much to ask.
It’s been several weeks since I did this so I am stretching the memory a little here but to add the checkboxes I selected Subtitle1 which selected the shopping item and then used Insert \ Checkbox that then added checkboxes to each item nicely.
After this to get text to grey out and strike through when the checkbox was ticked took a lot of trial and error but again choosing Subtitle 1 in the Advanced tab of the edit screen you can enter a conditional format like formula can against the Strikethrough property.
To get the two columns I used the strangely labeled wrap count which is one by default which I changed to two.
I think this is a great tool and there is great scope to build it on to your spreadsheet applications which still does the crunching so a user can see and update data on a mobile. Go to the Microsoft PowerApps site to check it out. It’s free with selected Office 365 Plans, not that dear at $7 per user per month for Plan 1 (if you have a compelling need) and free to trial in any case.