Blog Archives - TheSmartMethod.com

Publishers of the world’s most comprehensive and up-to-date Excel tutorials

Excel tutorials

The only Excel books you will ever need

The only constantly updated Excel 365 titles more…
Thoroughly covers Power Pivot and Power Query more…
Covers business intelligence and OLAP features more…
Simple, easy, understandable, 2-page lessons more…
Available as both printed books and e-Books more…
Used by schools, colleges and universities more…
Available for 365, 2021, 2019, 2016 and Mac versions more…

Category: Blog

Search again:

Excel protected view warning

Excel protected view: Be careful – files from the Internet can contain viruses. Unless you need to edit it’s safer to stay in Protected View. This article explains what this error message means.

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Microsoft Office update channels

Microsoft Office update channels are explained in this article. Beta, Current, Monthly Enterprise and Semi-annual enterprise are all covered.

Every lesson is presented on two facing pages

This article explains how Smart Method books efficiently transfer information by presenting every lesson on two facing sheets of A4 paper.

Excel linear and exponential series

Linear and exponential series are explained in this short tutorial that also shows how to model them using Excel. Sample file included.

Excel XLOOKUP function (part two)

This is the second of a two-part series of XLOOKUP lessons. If you haven’t already done so I recommend that you begin with part one by clicking this link. Excel

Power Pivot - 2 Minute Executive Overview

Excel Power Pivot – 2 minute overview

Power Pivot causes great confusion. This simple 2-minute overview completely de-mystifies Power Pivot and its associated technologies.

Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update. It isn’t available in older versions (Excel 2019 and earlier). The July 2020 update was

Excel SEQUENCE Function

This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.

Excel modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

Excel OLAP pivot tables

This article explains the difference between a regular Excel pivot table and the new OLAP pivot table used by Power Pivot.

Excel Power Pivot window

This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.

Excel Get & Transform overview

Get & Transform (or Power Query) is an advanced ETL tool. ETL is an acronym for Extract, Transform and Load. This article simply explains this amazing tool.

Excel data model Design

A Power Pivot data model is an OLAP database. OLAP databases can be modeled as a Snowflake or Star schema. This article explains both designs.

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Picture of man holding key over his head

Primary and Foreign Keys simply explained

Tables are related to each other using primary and foreign keys. This article explains how Excel uses them in Power Pivot and Get & Transform.

Excel CUBEVALUE function

This lesson shows how to use the CUBEVALUE function to make an OLAP pivot table directly access the data model. Includes sample file.

Excel 3D Maps data model

3D Maps (previously called Power Maps) enables an elegant visualization of data residing in a data model.This article explains how data models are utilized.

Our e-books are now published in Kindle e-book print replica format. This article explains why this e-book format is optimal for learning and includes screen grabs from the Kindle PC reader application.

Audio Problems and Solutions

This article explains the many reasons why you may not be able to hear audio when playing videos in a web browser.

Inserting rows and columns in pivot tables

There are five different ways to insert rows or columns into a pivot table. This article explains them all.

Excel formula to get working days between two dates excluding holidays

This article shows you how to calculate the number of working days between two dates in Excel using the NETWORKDAYS function.

Applying a unique constraint in Excel

This article shows how to impose a unique constraint in Excel, preventing duplicate items from being accidentally entered.

Remove dashes from ssn, keep zeros

This article shows you how to remove dashes from social security numbers by using Excel’s SUBSTITUTE function, with a downloadable example.

Printing the Basic Skills course

This article explains how to print and bind the Smart Method’s free Excel Basic Skills course along with the front cover graphics.

How to save and view a PDF e-book on an iPad

This article describes how to save PDF files to your iPad and add it to your iBooks library. You can then view the PDF offline.

Excel 2019 for Mac vs Excel 2019 for Windows

The most complete existing list of the differences between Excel 2019 for Windows and Excel 2019 for Mac. The Windows and Mac versions are very different.

Horizopntal scrolling with the mouse in Excel

Horizontal scrolling with the mouse in Excel

Most users know how to use the “mouse wheel” to scroll in a vertical direction.This article explains how you can also scroll horizontally using Auto Scroll.

How to split delimited data in Excel

This article describes three different ways of splitting delimited data in Excel, including Flash Fill, Text to Columns and formulas.

Keyboard shortcut for autofill in Excel

This article explains several different ways to carry out an AutoFill using the keyboard in Excel and explains Flash Fill and the Repeat command.

Why won’t the format change for a date?

This article explains why you may not be able to format a date in Excel and shows how to solve the problem using a formula and custom format.

How to add leading zeros in Excel

This article shows how to add leading zeros to numbers in Excel by using a custom format or by formatting numbers as text.

Duplicating tables to other worksheets

This article describes how to duplicate Excel tables to different workbooks, using copy and paste, formulas or the new Get & Transform feature.

Privacy Warning on a macro-enabled workbook

This article shows how to prevent a privacy warning being displayed whenever a workbook is saved by changing Excel’s Trust Center settings.

How to consolidate data and automatically keep it up to date in Excel

This article shows several different ways of consolidating data in Excel, including the Consolidate tool and the new Get & Transform feature.

Forcing Solver to return whole numbers

This article explains what the Excel Solver add-in does and how to create a constraint that will force it to return whole numbers.

Excel crashes after refreshing data

This article explains several reasons why Excel may crash after refreshing data and how to resolve these issues if they are affecting you.

Refreshing Slicers in Excel

This article explains how to change Excel’s Slicer settings to stop displaying items that have been deleted from the data source.

Missing Field List – Pivot Table

This article explains how to bring back the Pivot Table Field List if it has been closed or moved and explains a bug that can prevent it from appearing.

Can Excel Slicers be used in PowerPoint?

This article discusses Excel Slicers and how to integrate them with PowerPoint presentations and other Office applications.

Create a chart for the average and standard deviation in Excel

This article will show you how to create moving average and standard deviation charts in Excel and how to calculate this data using formulas.

Excel chart appears blank – not recognizing values?

This article shows you how to solve the problem if you find that values aren’t being recognized by Excel charts, including a sample workbook.

Excel IF formulas with multiple arguments?

This article explains the Excel IF function and how to best handle situations where multiple conditions are needed, including a sample workbook.

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Excel formula to get number of days in a month

This article will show you how to create an Excel formula that calculates the number of days in the month for any given date.

A formula or easy way to annualize data based on month

This article shows you the Excel formula to annualize data by month or by any other number of periods in a year. A sample workbook is included.

Changing dollar signs to GBP in Excel

This article shows you how to change currency symbols in Excel, how to convert from one currency to another and how to get exchange rate data online.

Using VLOOKUP when one column is blank get value from another column

Using VLOOKUP, if Column 1 is blank, get value from Column 2

This article shows how to create an Excel VLOOKUP formula that extracts data from a different column if the first column it searches is blank.

Adding images to tables

This article explains how to insert pictures into Excel workbooks and Excel’s current image features, including cell backgrounds.

A cell won’t let me enter data

This article explains several reasons why Excel may not allow you to enter data into cells, along with solutions that will enable you to fix the problem.

Macro buttons suddenly stopped working

This article explains why Excel macros can seem to suddenly stop working and suggests several possible solutions to the problem.

Adding “+” between words in Strings

Code numbers such as product codes are common in Excel. When you work with codes like these you often need to join multiple pieces of text (or ‘strings’).

Changing cell padding in Excel

This article explains how cell padding is implemented in Excel and how to resize and format cells to achieve padded layouts.

Table Tools, Design Tools Group

This article explains the Table Tools > Design tab on the Excel Ribbon, how to access it and how to reset the Ribbon if the tab has been disabled.

How to modify or delete calculated fields in Excel pivot tables

This article explains pivot table calculated fields and shows you how to modify or delete them using the Insert Calculated Field dialog.

Calculating the original number after a percentage increase

This article shows you how to use Excel to calculate the original number after a percentage increase – a surprisingly common problem in accounting.

Convert negative numbers to positive in Excel

This Excel article shows you how to convert negative numbers into positive numbers without affecting existing positive numbers.

How to hide data in a chart that is zero

I do not want to show data in chart that is “0” (zero)

This article shows how to hide zeros from Excel charts. It also shows how to control how hidden and empty cells are dealt with by your charts.

How to hide #N/A errors in Excel

This article explains the #N/A error code in Excel, why it occurs and how to hide it when it appears on your Excel worksheets.

How to use factorial (!) in Excel

This article shows how to calculate factorials and other combinatorials in Excel, showing how to calculate the probability of winning the lottery.

An Excel formula to get the week of the month

This article shows you how to create an Excel formula to get the week of month from a date, as well as how to get the week of year from a date.

Working with HEX values in Excel

This article explains the Hexadecimal counting system and how to work with it in Excel, as well as the four different counting systems supported by Excel.

Use the LAMBDA function and DYNAMIC ARRAYS to construct a roulette simulator

In this step-by-step tutorial Excel’s Lambda function and Dynamic Array feature are used to develop a full professional-grade Excel application.

Use the SUMIF and COUNTIF functions to create conditional totals

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

VLOOKUP using text strings

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

Why you can’t effectively learn Excel 2016/365 using a Video Tutorial

This article explains Microsoft’s new Sofware As A Service strategy for Excel 2016 via Office 365, as well as the future of Excel 2019 and beyond.

Separating text and numbers in Excel

This article shows three different ways of separating text and numbers in Excel. You’ll see how to do this using Flash Fill, Text to Columns and formulas.

Removing gaps between bars in an Excel chart

This article shows you how to change the width of the gaps between bars in Excel column charts, or remove the gaps entirely.

Adding VAT to a value in Excel

This article shows how percentages are handled in Excel workbooks and how to add 20% VAT to a value in Excel, along with an example.

An Excel function to check if a cell contains some text

An Excel formula to check if a cell contains some text

Sometimes you need to know whether a cell contains specific, or any text. This article shows how to do this using the SEARCH, ISNUMBER and FIND functions.

Subtracting percentages in Excel

This article explains how Excel handles percentages and shows you how to subtract a percentage from a number in Excel using two different methods.

The value doesn't match the data validation restrictions defined for this cell

Why does Excel say “The value you entered is not valid”?

This is happening because the cell has a Validation that only allows certain values to be entered. You can remove it by following these steps.

A Keyboard Shortcut for Merge and Center

You can create keyboard shortcuts to any command in Excel 2016 without needing any external tools or macros. This article will show you how.

Excel Versions Explained

This article describes the history of Microsoft Excel, from its earliest versions to the most recent, and the best ways to learn how to use each version.

Office 365 Updates

This article describes the new features added to Excel 365 for each new semi-annual version.

Excel 2016 for Mac vs Excel 2016 for Windows

The most complete existing list of the differences between Excel 2016 for Windows and Excel 2016 for Mac. The Windows and Mac versions are very different.

Why was my credit card not authorized?

There are many reasons why a credit card payment can fail. This article explains most of the possible reasons and how to resolve them.

Excel tutorials

Category: Blog

Categories

Recently added articles

Excel Tutorials

Articles

Downloads

Policies

About

Which tutorial do you need?

Which Excel Essential Skills version do you need to learn?

Excel 365 for Windows

Excel 2021 for Windows

Excel 2019 for Windows

Excel 2019 for Mac

Excel 2016 for Windows

Excel 2016 for Mac