Get & Transform (formerly known as Power Query) is perhaps the biggest hidden secret in Excel and the tool that can save you hours and hours of boring and repetitive work. To analyse data in Excel, in many cases you must first import it. This can be done manually or use VBA, but manually importing data into Excel is slow, tedious and error-prone and VBA requires some programming knowledge.

Get & Transform allows the automation of the process of importing data, but it requires no coding knowledge and tends to result in better performance. But it doesn’t only import data; Excel Get & Transform is an advanced ETL tool.  ETL is an acronym for Extract, Transform and Load.

It allows you to:

• Extract: Most data that is analysed in Excel is imported from an external data source. Extract means moving this data from the external data source into the Get & Transform tool. This can include Excel files, Text or CSV files, Databases such as Microsoft SQL Server, Microsoft Access and Microsoft Exchange, Facebook and even web pages.

• Transform: Extracted data often is often in a form that isn’t easily analysed. There may be unwanted columns, bad formatting or corrupted data. Get & Transform has a vast array of features that enable you to clean your data before loading to Excel table. The extracted data can be transformed in almost unlimited ways:

• • Removing columns
  • Filtering the data
  • Grouping the data,
  • Pivoting/unpivoting the data
  • Splitting up text
  • Extracting keywords from text
  • Add rows from another table
  • Remove Duplicate Records
  • Split a Column the Number of Chars
  • Duplicate Columns
  • Use First Row as Headers
  • Remove Columns
  • Remove Duplicates
  • Fill Down Values
  • Create Index Columns
  • Remove Rows With Errors
  • Group Rows and Get Counts
  • Reverse Rows
  • Transpose
  • Replace Values
  • Split First & Last Name
  • Consolidate Excel Workbooks or Worksheets

• Load: The transformed data from the Get & Transform tool is loaded into an Excel table or into an Excel Pivot Table, where it can be analysed and shared.

Whatever you have done, the whole transformation of the data process can then be saved as a Query. Perhaps the greatest and most time-saving feature of Get & Transform is that it allows you to set up a query once and then reuse it with a simple refresh. Get & Transform is very easy to use and pick up, more so than other Excel tools like formulas or VBA. And no coding knowledge is required. Get & Transform records all your transformations step by step. You can even go back and amend any of the steps individually.

Get & Transform is part of Infero’s new Data Analysis and Visualisation with Microsoft Excel Course. Contact us to learn more about it. The applications for your own work in Excel could be endless.

Excel dashboards are one-page (or rather one worksheet) summaries that contains important information which allows managers to track key KPIs, metrics and other data points in one visual, central place. They may be used in almost any industry, for almost any purpose. It is possible to have a Dashboard to track finances, for marketing or to show the progress of a key project.

Dashboards are made up of tables, charts, gauges and other visualisations. Able to display a high-level view of an organisation, a good dashboard will aid in making business decisions and can also provide a portal to keep everyone up to date, giving a real time view of current status or performance.

If you want to be able to extract useful, actionable organisational information from your raw data, and present it in a visually compelling format that will enable key trends to be seen and decisions to be made, then Infero Training’s Microsoft Excel: Dashboards Course will help you do this. 

Creating an Excel Dashboard is a multi-step process and our course takes you through all of these in an easy-to-follow manner. As well as all of the technical aspects, the course covers the key things you need to keep in mind when creating a dashboard and how you need to be clear about the objectives of the dashboard, before you even start using Excel.

The course also looks at simple Excel dashboard design rules, such as the simple use of colours, easily read, clean design and other considerations.

Excel Dashboard design should be:

• Understandable with no confusion over what each of the metrics mean.
• Interactive so that the Dashboard can be changed for different areas of an organisation, or to answer different questions.
• Clearly labelled, with all of the charts and tables on the dashboard displaying a clear message.
• Organised into a symmetrical grid that aligns sections into blocks of data.

Course Content

Lesson 1: Data Analysis Fundamentals

Topic A: Introduction to Data Science

Topic B: Create and Modify Tables

Topic C: Sort and Filter Data

Lesson 2: Visualizing Data with Excel

Topic A: Visualize Data with Charts

Topic B: Modify and Format Charts

Lesson 3: Analysing Data with Formulas and Functions

Topic A: Analyse Data with Formulas and Named Ranges

Topic B: Analyse Data with Functions

Lesson 4: Analysing Data with PivotTables

Topic A: Create a PivotTable

Topic B: Analyse PivotTable Data

Lesson 5: Presenting Visual Insights with Dashboards in Excel

Topic A: Visualize Data with PivotCharts

Topic B: Filter Data Using Slicers and Timelines

Topic C: Create a Dashboard in Excel

To learn more about this course, contact us today.

Yorkshire Day is a yearly celebration on the 1st August to promote the historic English county of Yorkshire.  Initially marked in 1975, the Yorkshire Ridings Society started the day as “a protest movement against the local government re-organisation of 1974”. The date was not chosen at random, but has historical significance being the anniversary of the emancipation of slaves in the British Empire in 1834. It was William Wilberforce, a Yorkshire MP, who had been one of the campaigners for this emancipation.

Many people travel to Yorkshire on Yorkshire Day to enjoy the good food, the amazing attractions and the incredible scenery of England’s biggest county and, after months of being shut, many Yorkshire attractions will be hopefully be open or re-opening on August 1st

Events

Tapton Hall  – Tapton Hall in Sheffield has usually celebrated this holiday with traditional music from local bands and a themed afternoon tea. Hopefully something similar will occur this year

There are sometimes Yorkshire Day Chocolate Workshops in Scarborough. This may happen again this year, but please check closer to the time. – What would Yorkshire Day be without a chocolate fountain or at least a proper Yorkshire Tea Cup.

Barnsley Museums – A number of museums and historic sites in around Barnsley. There are usually a number of Yorkshire Day activities planned. In the past these have including a Yorkshire pudding competition, a digital jigsaw and a Yorkshire sonnet, and more. Keep an eye on the website. 

Attractions

York Castle Museum – This museum covers hundreds of years of York’s social history in one place. Check on the Museum’s website closer to the date.

The Hepworth Wakefield – The Hepworth Wakefield is an award-winning art gallery in the heart of Yorkshire, set within Wakefield’s historic waterfront overlooking the River Calder. 

North Yorkshire Moors Railway – A preserved historic railway stretching for 18 miles through the heart of the North York Moors National Park. It is one of the largest preserved heritage railway in the UK.

York Art Gallery – Housed in an impressive historic building with an impressive collection, providing tickets are purchased in advance online. 

Rievaulx Abbey – Set in a valley in the North York Moors National Park with the impressive ruins of one of England’s most powerful Cistercian monasteries. The Abbey has a new visitor experience which looks at Rievaulx Abbey’s fascinating past. There is also a museum which will hopefully be open.

York itself is a great city to visit, with many attractions including the Jorvik Viking Centre. And of course, there are amazing walks in Yorkshire, including on the famous Ilkley Moor, with or without a hat.

Enjoy Yorkshire Day.

The BBC Arts Great British Creativity Test suggested that being creative is good for you and can help avoid stress and improve self-development. Multiple past studies confirm that being creative can increase positive emotions, decrease anxiety, and even improve immune system functioning. And more, a study published in the Journal of Occupational and Organizational Psychology said that engaging in creative activities outside of the office can result in better work performance.

“You Can’t Use Up Creativity. The More You Use, The More You Have”

– Maya Angelou

Being creative doesn’t have to be hard and if you think you are not creative, it is just not true.

“If someone tells you they cannot read and write, you don’t assume that they are not capable of reading and writing, but that they have not been taught. It is the same with creativity. When people say to me that they are not creative, I assume they just haven’t learned what’s involved.” –  Ken Robinson 

There are countless creative things that you can do, some achievable very easily (although some take a little more work, obviously); some that you can do right now and some that can be accomplished with things you already have. Here are twenty-five ideas to get you started:

1. Write a Song. Pen just the lyrics, or just the music or both.
2. Make Origami. There is plenty of help and ideas here.
3. Write A Short Story. Fiction. Nonfiction. Romance, western, sci-fi, contemporary. Whatever.
4. Draw A Picture. Use Crayons, paint, chalk, pencil, pens, or whatever you have to hand. You can draw digitally to on a tablet or laptop.
5. Work on a Book. Novel, nonfiction, whatever and whenever you want. It can be about anything you want it to be.
6. Get creative in your Garden with Flowers and Vegetables.
7. Write Some Poem. Poetry can be almost anything. Here’s one list of nearly 200 different forms of poetry.
8. Take Some Photos. Go to a park or some other interesting place near to you.
9. Build A Website. It can be on anything. Just Google ‘how to create a website’ for almost endless help.
10. Create a Playlist. Video or music. Or both. It’s up to you. Have different playlists for different moods.
11. Upcycle a Piece of Furniture. Get it from a market or from your own home. Change that old piece of kit into something spectacular
12. Get an Adult Colouring book. You can be incredibly creative just colouring in. You can even download pages to print out and colour.
13. Rearrange Your Home or Paint and Decorate. Your project can be small or large.
14. Try Candlemaking.
15. Make a Video. We all have smartphones with the ability to take high quality video that we never use. There are plenty of free apps for video editing Try Shotcut to start off with
16. What about Knitting, Needlework, Quilting or Crochet?  
17. Create a New Recipe and Get Creative in The Kitchen. The TV schedules are full of cookery shows, so there are plenty of ideas. 
18. Create a Photo Album. If you have a lot of photos hanging around, then buy an album and organise them. Or you can put those hundreds of digital photos into an online Photo Album
19. Start a Journal and write about anything and everything.
20. Buy a Model Kit and put it together and paint it.
21. Design Some Clothes or a Bag or Shoes. Or customise some you already have.
22. Make Some Recycled Art. Turn something you don’t use anymore into something great. It doesn’t have to cost a lot.
23. Restore Something. If you have an item that is broken or past it’s best, do a little research and get it back to where it was.
24. Research a Subject that you have always been interested in. You can use MS OneNote to organise what you find. (We have and upcoming blog on OneNote.)
25. Practice an Instrument. If you ever once started to learn an instrument and still have it lying around, then pick it up and practice a little.

“Creativity is Intelligence Having Fun!”​

– Albert Einstein

And there are lots more things you can try. Go on – get creative!

World Watercolour Month is celebration of the medium of watercolour painting, encouraging everyone to take up a brush and give this form of painting a try. Not only that, it is an event which encourages and supports arts education.

Watercolour is a painting method in which the paints are made of pigments suspended in a water-based solution and the term ‘watercolour’ refers to both the medium and the artwork itself. It’s an extremely ancient form of painting, dating perhaps to the cave paintings of palaeolithic Europe. It has been used for manuscript illustration since at least Egyptian times but especially in the European Middle Ages.

World watercolour month was founded by Charlie O’Shields, creator of the Doodlewash, a website dedicated to all things watercolour. The event Partners with the Dreaming Zebra Foundation which provides help to those who may otherwise not be able to develop their artistic creativity.  They are a non-profit organisation that encourage children’s individuality, help them to express themselves creatively, and to follow their dreams within art. The aim is to raise awareness and to raise funds for this worthwhile cause.

Dreaming Zebra also have an:

“..art recycling program that is free to the public. Reusable art & music supplies that would otherwise be discarded, along with new or unsold materials, are donated by individuals and businesses and matched to recipients who have requested those materials for arts education purposes in the community.”

Art is important to us all. Many would argue that it absolutely essential. It has always played an important part in education, being a great leveller and being one of the things that everyone has access to and that everyone can take part in. It’s for this reason that it is so terrible that it is often the case that art classes are first in line when budget cuts are made in education.

During World Watercolour month you can take the ultimate challenge of painting 31 watercolours in 31 days. Even if you are not up to that, you can dust off your old palette and have a go. Watercolour paints are available very cheaply in many shops and also online, so there is not excuse. If you need tips, try here.

You can post your masterpiece online with the hashtag #WorldWatercolorMonth. And, if you can, donate to The Dreaming Zebra Foundation to help children across the world reach their artistic potential.

Happy painting!