Let’s take a look. To illustrate how Shrink to fit works, let’s look at a typical layout problem. Here we have a basic feature table that needs some clean up. Let’s start by applying a horizontal and vertical alignment of center for all cells. We don’t have a lot of long text in this table, so center alignment will work well. Now let’s increase the row height to get some more white space into the layout....

As you work in Excel, you’ll often spend a considerable amount of time formatting data to get it to look good. And although formatting tools in Excel are easy to use, applying formatting manually can quickly get tedious. One tool you can use to speed things up is the Format Painter. The Format Painter copies formatting from one group of cells to another. In this example, I have a table that has a variety of custom formats applied: the header has a color fill and a white font; the movie name is in italics; year and rank are centered; the movie length is formatted as a time; and all the data cells have a gray border....

where data is the named range B5:D10. If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array: Once we have the first column, we can add the total columns in the range and subtract 1 to get the last column number. Index version Instead of MIN, you can also use INDEX to get the last row number: This is possibly a bit faster for large ranges, since INDEX just supplies a single cell to COLUMN....

In the example shown, the formula in G7 is: Where “Sex” is a named range for C3:C15 and “Time” is the named range D3:D15. Note: this is an array formula and must be entered using Control + Shift + Enter. The problem in this case is that we don’t want SMALL to operate on every value in the range, just values that are either male or female (M or F)....

where data is the named range B5:B15. The result is 7, since the number in B11 (the seventh row in data) contains 99. This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. One solution is to convert the numeric values to text with the TEXT function like this: This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365....

Fields In the pivot table shown, there are four fields in the source data: Date, Sales, Week, and Filter. Filter is a helper column with a formula flagging the last 4 weeks. Week is a helper column that returns the Monday for any given date (for convenience only). Sales is the value field, and Week has been added as a Row field to group by week: Formula The formula used in D5 (Week), copied down, is: This formula returns the Monday of week for any given date....

Saving A New Workbook The First Time If you’ve created a new workbook and haven’t saved it yet, then you will notice at the top of the workbook Excel has named the file for you with its generic naming Book1, Book2 etc… When saving a workbook for the first time you will need to use the Save As command to give the workbook a name and location to save it....

First, the keyboard shortcut for editing a cell is F2 on Windows, and Control + U on a Mac. With Excel’s default settings, this will put your cursor directly in the cell, ready to edit. You can also double-click a cell to edit. If you want to always edit cells in the formula bar, even when you use a keyboard shortcut to edit a cell, you’ll need to change a setting....

where data is an Excel Table in the range B5:C16. The result is the sum of the top 3 values in Group A (65,45,25) which is 135. Note: FILTER is a newer function not available in all versions of Excel. See below for an alternative formula that works in older versions. This problem can be solved with a formula based on the FILTER function, the LARGE function, and the SUM function....

where data (D6:H16), diameter (D5:H5), material (B6:B16), and hardness (C6:C16) are named ranges used for convenience only. Note: this is an array formula and must be entered with Control + Shift + Enter This can be done with a two-way INDEX and MATCH formula. One MATCH function works out the row number (material and hardness), and the other MATCH function finds the column number (diameter). The INDEX function returns the final result....

Here we have a simple cost calculator which looks up cost based on a material’s width and height. The match needs to be approximate. For example, if the width is 250, and the height is 325, the correct result is $1,800. If the width is 450, and the height stays at 325, the correct result is $3,600. We can build a formula that does this lookup using INDEX and MATCH....

So, today we’re going to tackle the question Why Pivot Tables? And this question comes up a lot when people first run into Pivot Tables, because they’re wondering…why should they care? And you’re trying to explain that pivot tables are really fast, that you can summarize data interactively in different ways…blah, blah blah And you can kind of see their eyes just glaze over because, until you’ve actually used a pivot table yourself, it’s really hard to understand why they would be useful to you....

Once you have a GST number, you need to file your tax return every month (in India). So every month, my Charted Accountant would reach out to me asking for sales invoices so that he can file for the GST. In my case, there only a handful of invoices to be created, as I have only a few sources of income. However, since this is additional work, I wanted to get this done as quickly as possible....

Excel allows users to create multiple sheets in their workbook solutions. But you might only want the user to see a selection of those sheets. Excel lets you hide any sheets you don’t need. This is a great way to organize your work and make sure that only the essential information is visible. This can help to declutter your workbook and focus the user’s attention on the information they need to see and make it easier to navigate your workbook....

Microsoft Excel is a powerful spreadsheet application that offers users a variety of features and capabilities. One of the most popular features of Excel is the ability to create and run VBA macros. A macro is a small scripts written in the VBA (Visual Basic for Applications) programming language that can be run in your desktop Excel app. Macros can save you a lot of time and energy when working in Excel....

These are keyboard shortcuts for moving the active cell around a selected range of cells. Enter – Move the active cell one cell in a direction based on your settings. This is down by default.Shift + Enter – Moves the active cell up to the previous cell in the range.Tab – Moves the active cell to the right in the range.Shift + Tab – Moves the active cell to the left in the range....

Pivot tables are one of Excel’s most powerful tools. They allow you to quickly turn thousands of rows of data into meaningful summaries with an easy to use drag and drop interface. In the example we’re going to go through, we will look at sales data with 5,000 records. Each record or row of data pertains to one sale to one customer and contains the follow information. Sales Representative – This is the person who made the sale....

This formula adds 7 workdays days to Tuesday, Dec 22. Three holidays are supplied using the named range “holidays” (B9:B11) and weekends are set using the special syntax “0000000” which means all days in a week are workdays. The result is Thu, December 31, 2015. The weekend argument is supplied as 7 characters that represent Monday-Sunday. Use one (1) to indicate weekend, and zero (0) to indicate a working day....

This is the same formula used to highlight entire rows in the table using a formula-based conditional formatting rule. The start date must be less than or equal (<=) to at least one other end date and the list. The end date for the project must be greater than or equal to (>=) at least one other start date in the list. If both of these conditions are true, the project dates must overlap another project in that list....

where data is the named range B4:F15. The result is 4, the number of columns that contain the number 19. Note: this is an array formula and must be entered with control shift enter in Legacy Excel. See below for a formula that performs the same task with the newer BYCOL function. MMULT option One option for solving this problem is the MMULT function. The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”....