This means a lot of people are working from home now and need to use some sort of tool to collaborate with their teams. If your company is on Microsoft Office 365, then you’re likely using Microsoft Teams. In this post you’ll learn the top 25 tips and tricks for working with Microsoft Teams. Video Tutorial Allow External Guest Users in Teams Teams is an amazing tool for collaborating on your work, but by default you can only invite internal users to a team....

However, a random number generator does have a huge use when working with different scenarios on a set of data or when performing various statistical analyses. A financial model may use a stochastic simulation that is dependent on probabilities. The model may need to be run thousands of times, but with the random number generator providing the parameters of each simulation. Whatever your need for random numbers, Excel has several ways to generate them....

This post is going to show you all the ways you can use Microsoft Excel for free in a totally legitimate and legal way from Microsoft. Microsoft Excel is a spreadsheet program that is part of the Microsoft Office suite of productivity software. Excel allows users to import, manipulate and visualize data. It’s a powerful tool for data analysis and can be used to create charts, graphs, and pivot tables....

Syntax HLOOKUP(Criteria, Range, Row, Type) Criteria (required) – This is the value you are going to try to find.Range (required) – This is the range of cells that you want to search.Row (required) – This is the row number of the range that contains the result you want to return.Type (optional) – This is the type of match you are looking for. FALSE if you want an exact match and TRUE if you want an approximate match....

Example Generic Formula There are two possible solutions for any quadratic equation. This is one possible solution. This is the other possible solution. What It Does These formulas will give the solutions to a quadratic equation of the form Ax^2 + Bx + C = 0. How It Works This is a simple algebraic formula and uses the SQRT function which returns the square root of a given number and the ^ operator which raises a given number to a given power....

Every month he has to get some data from a system and use that in his month end finance work. This part of the system has no export button. The only way to get the data is to highlight it on screen and copy and paste the data into an Excel sheet. Instead of copying into a nice table like it’s displayed on screen it copies into a single vertical column....

Power Pivot is a tool available in Excel that will help you analyze and summarize large amounts of data. But you will first need to install the add-in to use it. This post will show you the different methods you can use to install Power Pivot in Excel. What is Power Pivot in Excel? Power Pivot is an Excel add-in that lets users perform powerful data analysis. It allows users to connect to multiple data tables, define relationships between these tables, combine them into a single data model, and then create calculations to perform complex analyses....

To do this we will need to set up a few items in our workbook. A cell for our genre selectionA range for our list of genresRanges for each list of films within each genre We are going to let the user select from these three genres: Drama, Comedy and Horror. Now when you select a genre, the drop down list for the title selection will change depending on the genre....

Excel is the most common data tool used in businesses and personal productivity across the world. Since Excel is so widely used and available, it tends to get used frequently to store and manage data as a makeshift database. This is especially true with small businesses since there is no budget or expertise available for more suitable tools. This post will show you what a database is and the best practices you should follow if you’re going to try and use Excel as a database....

With a bit of VBA know-how, it can be done fairly quickly. Writing the code to loop through all the sheet objects in the active workbook and write them out to a sheet would only take a dozen lines of code. What if you needed to this for all workbooks in a given folder? Hmm, a bit more code and it’s possible. You first need to loop through all the workbooks in a folder, then for each one loop through all the sheets and write the names out to a workbook....

A lot of people don’t know that Excel has an options menu. The Excel Options menu contains a wealth of essential features that allow you to customize your Excel experience. The Excel Options menu will allow you to customize your ribbon, change calculation settings, change language settings, and change many other workbook and app settings. In this blog post, we’ll show you how to open the options menu so you can access the various useful features it contains....

Printing your Excel reports is a very common activity. But when you try to print your Excel document, you will not see any of the light gray gridlines that appear in your sheets. This is because the default print settings will exclude them. What if you want to include these gridlines on the printouts? This can help the reader to differentiate between separate pieces of data. You can change this setting so the gridlines do appear in any printed items whether it’s printing to paper or PDF files....

For example, given the 2020-04-23 can you return the value April? Yes, of course you can! Excel can do it all. In this post you’ll learn 8 ways you can get the month name from a date value. Long Date Format You can get the month name by formatting your dates! Good news, this is super easy to do! Format your dates: This will format the date 2020-04-23 as April 23, 2020, so you’ll be able to see the full English month name....

Since it was a small list of pages and the list was static, I decided to use the Enter Data feature. In this post, we’re going to look at how to copy and paste data from an Excel workbook or text file into Power BI using the Enter Data feature. Video Tutorial Why Use The Enter Data Feature? This feature would usually only be used for a supplementary data table....

Example Generic Formula What It Does This formula will add leading zeros to a given number up to a specified number of total characters in the result. How It Works REPT(“0”,N) will create a text string with N zeros. In our example, REPT(“0”,6) results in a text string of 6 zeros “000000“. The TEXT(Number,”000000″) function will then format any given number as a text string with up to 6 characters for leading zeros....

Simple tasks like listing out all sheets, sorting sheets alphabetically, sorting sheets by colour, hiding and unhiding sheets are all commonly done tasks in a workbook. Unforntunately, the options in Excel to do this are mostly manual. These are some common sheet tasks that we can automate with a bit of VBA code! Sorting Sheets Alphabetically You can easily reorder your sheets by drag and drop or you can right click and use the Move or Copy menu....

This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice. Example In this example we have a list of email addresses with a Y or N in the next column depending on if the person is planning to attend the national dinosaur convention. Now we want to get a comma separated list of the email addresses for those who plan to attend....

Many applications come with fonts that are automatically installed with the program. Microsoft Excel is no exception and comes with many fonts that you can use across all your spreadsheets. Although Excel comes with a large selection of fonts to choose from, you may want to install a custom font that you’ve either created yourself, or found elsewhere. Here’s how you can add new fonts to Excel. Free Custom Fonts Online One of the most important aspects of any document is its font....

Data Validation is a very useful Excel tool. It often goes unnoticed as Excel users are eager to learn the highs of PivotTables, charts and formulas. It controls what can be input into a cell, to ensure its accuracy and consistency. A very important job when working with data. In this blog post we will explore 11 useful examples of what Data validation can do. To apply these Data Validation rules;...

The problem was that she needed to display the name, street, city, country and post code each on a separate line within one cell. In this post we’re going to take a look at five different ways we can concatenate the data and separate each item with line breaks. In other words, we will combine or join data from multiple cells into one cell and separate them with line breaks....