Microsoft Excel: Tips and Tricks

Here’s the challenge: you are going to randomly assign each of 20 new students to one of the four houses. You will then populate a (between -100 and 100) to a random student. You will show both the student name and house on the scoreboard. And then you will show a list of unique students who scored points. In our solution, we use the INDEX, RANDBETWEEN, ROWS, XLOOKUP, and UNIQUE functions.

The VLOOKUP function is used to pull a value from a list or table based on a corresponding value. For example, if you have a worksheet with a table showing employee names, hire date, and salary, you could use VLOOKUP in a separate worksheet to pull the hire date and salary for individual employees from the first worksheet. In this example, the employee name serves as a key, identifying which information from the first worksheet you wish to pull.

It's easy to group and ungroup data in any list in Excel. Just follow these simple steps.

If your Excel data follows a pattern, you can use the Fill Handle to quickly and easily add data to multiple rows and columns.

Follow these five steps to record a macro in Excel:

Use the Quick Analysis tool to convert your data quickly and easily into a chart or table.

By default, numbers in Microsoft Excel do not show commas and do show the first two decimals (unless they are 0). However, numbers can be formatted to appear in many different ways.

You can easily add a wide variety of shapes to your Microsoft Excel worksheets, including squares, circles, stars, arrows, smiley faces, lines, equation shapes, and callouts. To insert a shape into a worksheet, follow these four easy steps.

You can quickly name cells in Microsoft Excel using their row and column headings.

Slicers make it really easy to filter data in a pivot table. Adding a Slicer is similar to adding a second Page field (Report Filter) but is even easier to use. To add a Slicer to a pivot table, follow these four steps.

The following seven steps show how to create a pivot table timeline for filtering your Excel data by time.

You can insert a chart into an Excel workbook in just a few simple steps.

If you need your workbook to be available to an international audience, follow these steps for displaying data in a different international format in Excel.

Converting a list to a table makes it very easy to format your data and to keep the formatting consistent as you make changes to the data. You can convert a list to a table in three short steps.

The LEFT and RIGHT functions are used to return characters from the start or end of a string. The following explains how to select these functions and some possible uses.

Creating a macro and adding it to the Quick Access Toolbar makes it easier to complete a set of operations that you perform regularly. To add a macro to the Quick Access Toolbar, follow these steps.

To select a range of cells in Microsoft Excel, just follow these three steps.

When working in Excel, it can sometimes be helpful to split the worksheet in two, so that you can compare data within the sheet. To split the window in Excel follow these three easy steps.

The following steps show how to insert a pivot table. A pivot table is an interactive table that can be used to analyze, organize, and summarize large amounts of data. Pivot tables make it easy to rearrange data, quickly displaying it in different ways. Before inserting a pivot table, make sure the data you want to analyze is in an organized table and that your data includes a header row, no empty rows or columns, and no subtotals.

Ink Equations allows you to "write" equations on a tablet device using a stylus or your finger and then converts them to text. If you are not using a touchscreen device, you can do the same using a mouse. To employ Ink Equations just follow these few steps.

Creating a formula that spans multiple Excel worksheets is easy. Just follow these steps.

The CONCATENATE function is used to join the contents of multiple cells. For example, if you have a worksheet with first names in one column and last names in another column, you can use the CONCATENATE function to join the first and last names into one column. To use this handy function, follow these three steps.

In Excel, you can use the TEXT() function to output the day name from a date. For example, if cell A1 contains 12/18/2017, you can output "Monday" in another cell using TEXT(A1,"dddd"). But I needed a way to list 10 day names in a row without any associated dates. And I wanted the list to update based on the value in the first cell. So, how do you do that?

Freezing panes is a way of making one or more rows or columns stay at the top or left of your worksheet as you scroll through the worksheet. A common use of freezing panes is to keep a header row in view as you scroll through a large worksheet. To freeze panes in an Excel worksheet, follow these four steps.