Data in Excel 2016
How to remove the diamond with question mark symbol
When you paste or import data into the Excel spreadsheet, you might notice unusual symbols like
a diamond with a question mark. The replacement character � (often a black
diamond with a white question mark or an empty square box) represents an unknown, unrecognized
or unrepresentable character:
How to create a dependent drop-down list
The
drop-down list
is a handy feature provided by Excel data validation tools. Drop-down menus limit the possible
choices and help to avoid mistakes. However, not so many Excel users know the Source field
of the Data Validation dialog is a formula field. Formulas in the Source field
should return the list of the values, but they are not necessarily a range or a list of options.
How to create a computable drop-down list
Dynamic, dependable, conditional, or calculated drop-down list in Excel implies that the
contents of the drop-down list for the current cell should depend on values in the other cell
and change dynamically when the value of the controlling cell changes.
Creating and using named ranges
Named ranges are a powerful Excel feature that allows you to assign a symbolic name for the
cell or the range of cells for later use as a convenient replacement for cell/range address in
formulas.
Creating a drop-down list in a cell
Manual data entry into spreadsheets and especially entering data into text cells
leads to many errors. Excel data validation tools allow you to reduce the inaccuracy
of Excel data entry by restricting the set of possible values to a pre-defined list.
Users can select the item from a drop-down list with few choices, instead of typing
text into the data entry form template.
How to create an automatically extended or dynamic drop-down list
When you work with data in Excel, you often need to add more and more new data. However, it
takes some time to update formulas, charts, tables, when you add new data. Excel proposes functions
that can help to customize your data and avoid routine updating. This tip is about creating an
automatically extended (also known as dynamically updated or just dynamic)
drop-down list.
How to copy computed subtotals in Excel
When you compute subtotals in your spreadsheet, it can be necessary to select only subtotals and
copy them to another sheet for future processing. If you select subtotals and copy them using
copy-paste (Ctrl+C and Ctrl+V), Excel pastes all source data with subtotals. This tip
demonstrates the workaround that allows you to copy just rows with subtotals.
How to select a large data range in one click in Excel
Sometimes you need to select a large data range. This tip shows how to select rows, columns, rectangular,
or free form ranges in a few clicks.
How to show image linked to the list element
Microsoft Excel proposes many different features for creating a perfect presentation of your data.
One of these features is linked elements. This tip shows how to create a drop-down list and automatically
show linked objects, such as images, text, hyperlinks, etc., for the selected drop-down list item.
How to create a Scrolling chart
Sometimes you need to create a chart that shows a large data amount. Then it would be useful to insert a
scroll bar in your chart: