Data and AI Training

Home | Prices | Contact Us | Courses: Power BI - Excel - Python - SQL - Generative AI - Visualising Data - Analysing Data

Tools for Data Analysis

There are lots of great tools for data analysis. This section highlights a few of these. During the course the instructor will demo some of these tools and attendees will use these in lab exercises.

Excel

Excel is a powerful tool for data analysis, especially for smaller datasets. Excel can help us with data manipulation, statistical analysis, and visualisation, for example:

• store tabular data in Excel tables
• summarise and explore data with pivot tables
• summarise and present data with functions such as SUMIFS
• help visualise and gain insight into a large table of numbers with conditional formatting (data bars and background colours, icons)

Excel offers three separate and usefully distinct tools for data analysis. These are:

• the Excel formula language
• Power Query, that offers and user interface and cleaning and shaping data in a repeatable fashion
• Python, the de-facto language for data analysis, data science and AI. (This is available in Excel’s current channel although not all organisations have yet deployed this version so may not be available to all attendees)
• an embedded generative AI model, Copilot, which can help us with our data analysis. It can also help us to use Python - for example to build charts better and faster than using the standard charting options.

Python

Python is a very popular open-source language for data analysis (and also machine learning and AI). It has a vast number of libraries available, such as pandas and numpy for data manipulation, matplotlib and seaborn for data visualisation

Many people explore data with Python using Jupyter notebooks. They provide an interactive and flexible environment for coding, documentation, and visualisation all in one place. Users can write and execute code in cells: this helps iterative exploration and immediate feedback on results, Data visualisations are inline making it easier to interpret data through graphs and charts. We can write documentation in Markdown which allows good presentation of any documentation, analysis and results.

Generative AI assistants for data analysis

Generative AI assistants include large language models such as ChatGPT, Gemini, Claude. These can explore and analyse datasets. Some tools such as ChatGPT have specialised versions (a custom GPT) such as Data Analyst.

Power BI Desktop

Power BI is Microsoft’s flagship tool for business intelligence. It contains Power Query, a very helpful tool for cleaning and shaping data.

SQL

SQL is the languages used to analyse and manage the data in most databases. It is an ANSI standard and so is common across databases from many different manufacturers.