-->
Learn how to apply conditional formatting in Excel. Also find information on clearing, copying, and pasting conditional formatting. View Notes - Punto-de-Equilibrio-en-Excel (1) from ACCOUNTING 001 at Saint Mary's. 54 107 161-4000-2000 2000 4000 6000 8000 10000 $ Ventas Costo. Punto de Venta Excel 2010 descargar Punto de Venta con Imagenes Punto de Venta con Catalogo de Productos Punto de Venta en Excel Facil Punto de Venta Facil Punto de Venta Gratis Punto de Venta para Tiendas Sistema de Ventas Sistema de Ventas para Tiendas Sistema de Ventas en Excel Sistema de Ventas Incoming search terms: como hacer un punto.
Es habitual tener los datos repartidos en varios orígenes de datos como, por ejemplo, la información de producto en una base de datos y la información de ventas en otra.It's common to have data spread across multiple data sources, such as product information in one database and sales information in another.Con Power BI Desktop, puede combinar datos de orígenes diferentes para crear interesantes y atractivos análisis de datos y visualizaciones.With Power BI Desktop, you can combine data from different sources to create interesting, compelling data analyses and visualizations.
En este tutorial, aprenderá a combinar datos de dos orígenes de datos: un libro de Excel que incluye la información de producto y una fuente de OData que contiene los datos de pedidos.In this tutorial, you learn how to combine data from two data sources: an Excel workbook that includes product information, and an OData feed that contains orders data.Después de importar cada conjunto de datos y realizar los pasos de transformación y agregación, use los datos de ambos orígenes para generar un informe de análisis de ventas con visualizaciones interactivas.After you import each dataset and perform transformation and aggregation steps, you use data from both sources to produce a sales analysis report with interactive visualizations.Estas técnicas pueden aplicarse también a consultas de SQL Server, archivos CSV y cualquier otro origen de datos en Power BI Desktop.These techniques can also be applied to SQL Server queries, CSV files, and any other data sources in Power BI Desktop.
Nota
En Power BI Desktop, a menudo hay varias maneras de realizar una tarea.In Power BI Desktop, there are often a few ways to accomplish a task.Por ejemplo, muchas de las selecciones de la cinta de opciones también están disponibles mediante el uso de un menú contextual o el menú Más opciones en una columna o celda.For example, many ribbon selections are also available by using a right-click or More options menu on a column or cell.En los pasos siguientes se describen varios métodos alternativos.Several alternate methods are described in the steps below.
Importación de los datos de producto de ExcelImport the product data from Excel
En primer lugar, importe los datos de producto desde el libro de Excel Products.xlsx en Power BI Desktop.First, import the product data from the Excel Products.xlsx workbook into Power BI Desktop.
Descargue el libro de Excel Products.xlsx y guárdelo como Products.xlsx.Download the Products.xlsx Excel workbook, and save it as Products.xlsx.
Seleccione la flecha desplegable junto a Obtener datos en la pestaña Inicio de la cinta de opciones de Power BI Desktop y luego seleccione Excel en el menú desplegable Más comunes.Select the dropdown arrow next to Get Data in the Home tab of the Power BI Desktop ribbon, and then select Excel from the Most Common dropdown.
Nota
También puede seleccionar el elemento Obtener datos o seleccionar Obtener datos en el cuadro de diálogo Introducción de Power BI, seleccionar después Excel o Archivo > Excel en el cuadro de diálogo Obtener datos y luego seleccionar Conectar.You can also select the Get Data item itself, or select Get Data from the Power BI Get started dialog, then select Excel or File > Excel in the Get Data dialog box, and then select Connect.
En el cuadro de diálogo Abrir, vaya al archivo Products.xlsx y selecciónelo y luego seleccione Abrir.In the Open dialog box, navigate to and select the Products.xlsx file, and then select Open.
En el panel Navegador , seleccione la tabla Productos y, a continuación, seleccione Editar.In the Navigator pane, select the Products table and then select Edit.
Se abre una vista previa de la tabla en el Editor de Power Query, donde puede aplicar transformaciones para limpiar los datos.A preview of the table opens in the Power Query Editor, where you can apply transformations to clean up the data.
Nota
También puede abrir el Editor de Power Query si selecciona Editar consultas > Editar consultas en la cinta de opciones Inicio de Power BI Desktop; también puede hacer clic con el botón derecho o seleccionar Más opciones junto a cualquier consulta de Vista de informe y seleccionar Editar consulta.You can also open the Power Query Editor by selecting Edit Queries > Edit Queries from the Home ribbon in Power BI Desktop, or by right-clicking or choosing More options next to any query in Report View, and selecting Edit Query.
Limpieza de columnas de productosClean up the products columns
El informe combinado solo usará las columnas ProductID, ProductName, QuantityPerUnit y UnitsInStock del libro de Excel, para que pueda quitar las otras columnas.Your combined report will only use the ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns from the Excel workbook, so you can remove the other columns.
En el Editor de Power Query, seleccione las columnas ProductID, ProductName, QuantityPerUnit y UnitsInStock (use Ctrl+Clic para seleccionar más de una columna, o Mayús+Clic para seleccionar columnas contiguas entre sí).In Power Query Editor, select the ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns (use Ctrl+Click to select more than one column, or Shift+Click to select columns that are next to each other).
Haga clic en cualquiera de los encabezados seleccionados y seleccione Quitar otras columnas en la lista desplegable, para quitar todo excepto las columnas seleccionadas de la tabla.Right-click any of the selected headers and select Remove Other Columns from the dropdown, to remove all except the selected columns from the table.También puede seleccionar Quitar columnas > Quitar otras columnas desde el grupo Administrar columnas de la pestaña Inicio de la cinta de opciones.You can also select Remove Columns > Remove Other Columns from the Manage Columns group in the Home ribbon tab.
Importación de datos de pedidos desde una fuente de ODataImport the order data from an OData feed
A continuación, importe los datos de pedidos de la fuente de OData del sistema de ventas de Northwind de ejemplo.Next, import the order data from the sample Northwind sales system OData feed.
En el Editor de Power Query, seleccione Nuevo origen y luego seleccione Fuente de OData en el menú desplegable Más comunes.In Power Query Editor, select New Source and then select OData feed from the Most Common dropdown.
En el cuadro de diálogo Fuente de OData, pegue la dirección URL de la fuente de OData de Northwind,
http://services.odata.org/V3/Northwind/Northwind.svc/, y seleccione Aceptar.In the OData Feed dialog box, paste the URL for the Northwind OData feed,http://services.odata.org/V3/Northwind/Northwind.svc/, and select OK.En el panel Navegador, seleccione la tabla Orders y luego seleccione Aceptar para cargar los datos en el Editor de Power Query.In the Navigator pane, select the Orders table, and then select OK to load the data into Power Query Editor.
Nota
En Navegador, puede seleccionar cualquier nombre de tabla, sin marcar la casilla, para obtener una vista previa.In Navigator, you can select any table name, without selecting the checkbox, to see a preview.
Expansión de los datos del pedidoExpand the order data
Cuando se conecta a orígenes de datos que tienen varias tablas, como bases de datos relacionales o la fuente de OData de Northwind, puede utilizar referencias entre tablas para crear las consultas.When you connect to data sources that have multiple tables, such as relational databases or the Northwind OData feed, you can use references between tables to build up your queries.La tabla Orders contiene referencias a varias tablas relacionadas.The Orders table contains references to several related tables.Puede agregar las columnas ProductID, UnitPrice, y Quantity de la tabla relacionada Order_Details en la tabla principal (Orders) mediante la operación Expandir.You can add the ProductID, UnitPrice, and Quantity columns from the related Order_Details table into the subject (Orders) table by using the Expand operation.
Desplácese hacia la derecha en la tabla Orders hasta que pueda ver la columna Order_Details.Scroll to the right in the Orders table until you can see the Order_Details column.Tenga en cuenta que, en lugar de datos, contiene referencias a otra tabla.Note that instead of data, it contains references to another table.
Seleccione el icono Expandir () en el encabezado de columna Order_Details.Select the Expand icon () in the Order_Details column header.
En el menú desplegable Expandir :In the Expand drop-down:
Seleccione (Seleccionar todas las columnas) para borrar todas las columnas.Select (Select All Columns) to clear all columns.
Seleccione ProductID, UnitPrice y Quantity, y luego seleccione Aceptar.Select ProductID, UnitPrice, and Quantity, and then select OK.
Después de expandir la tabla Order_Details, la columna Order_Details se reemplaza con las tres columnas nuevas de la tabla anidada, y hay filas nuevas en la tabla para los datos agregados de cada pedido.After you expand the Order_Details table, the Order_Details column is replaced by the three new columns from the nested table, and there are new rows in the table for the added data from each order.
Creación de una columna calculada personalizadaCreate a custom calculated column
El Editor de Power Query le permite crear cálculos y campos personalizados para enriquecer los datos.Power Query Editor lets you create calculations and custom fields to enrich your data.Se creará una columna personalizada que calcula el precio total de cada artículo de línea en un orden multiplicando el precio unitario por la cantidad de artículos.You will create a custom column that calculates the total price for each line item in an order by multiplying the unit price by item quantity.
En la pestaña Agregar columna de la cinta de opciones del Editor de Power Query, seleccione Columna personalizada.In the Add Column ribbon tab of Power Query Editor, select Custom Column.
En el cuadro de diálogo Columna personalizada, escriba LineTotal en el campo Nuevo nombre de columna.In the Custom Column dialog box, type LineTotal in the New column name field.
En el campo Fórmula de columna personalizada después de = , escriba [Order_Details.UnitPrice] * [Order_Details.Quantity] .In the Custom column formula field after the = , enter [Order_Details.UnitPrice] * [Order_Details.Quantity].(También puede seleccionar los nombres de campo en el cuadro de desplazamiento Columnas disponibles y seleccionar << Insertar, en lugar de escribirlos).(You can also select the field names from the Available columns scroll box and select << Insert, instead of typing them.)
Seleccione Aceptar.Select OK.
El nuevo campo LineTotal aparece como la última columna de la tabla Orders.The new LineTotal field appears as the last column in the Orders table.
Definición del tipo de datos del nuevo campoSet the data type for the new field
Cuando el Editor de Power Query se conecta a los datos, determina el mejor tipo de datos para cada campo y muestra los datos correspondientes.When Power Query Editor connects to data, it determines the best data type for each field and displays the data accordingly.Puede ver los tipos de datos asignados a los campos mediante los iconos de los encabezados, o bien en Tipo de datos en el grupo Transformar de la pestaña Inicio de la cinta de opciones.You can see the data types assigned to fields by the icons in the headers, or under Data Type in the Transform group of the Home ribbon tab.
La nueva columna LineTotal tiene el tipo de datos Cualquiera, pero los valores son de moneda.Your new LineTotal column has a data type of Any, but its values are currency.Para asignar un tipo de datos, haga clic con el botón derecho en el encabezado de columna LineTotal, seleccione Cambiar tipo de datos en el menú desplegable y luego seleccione Número decimal fijo.To assign a data type, right-click the LineTotal column header, select Change Data Type from the dropdown, and then select Fixed decimal number.
Nota
También puede seleccionar la columna LineTotal, luego seleccionar la flecha desplegable junto a Tipo de datos en la sección Transformar de la pestaña Inicio de la cinta de opciones y seleccionar Número decimal fijo.You can also select the LineTotal column, then select the dropdown arrow next to Data Type in the Transform area of the Home ribbon tab, and then select Fixed decimal number.
Limpieza de las columnas de pedidosClean up the orders columns
Para que sea más fácil que el modelo funcione con los informes, puede eliminar algunas columnas, cambiarles el nombre y reordenarlas.To make your model easier to work with in reports, you can delete, rename, and reorder some of the columns.
El informe solo usará las columnas OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice y Order_Details.Quantity.Your report will only use the OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns.Puede seleccionar estas columnas y usar Quitar otras columnas como hizo con los datos de Excel, o bien puede seleccionar todas las columnas excepto las que aparecen, hacer clic con el botón derecho en las columnas seleccionadas y seleccionar Quitar columnas para quitarlas todas.You can select these columns and use Remove Other Columns as you did with the Excel data, or you can select all the columns except those listed, right-click on one of the selected columns, and select Remove Columns to remove them all.
Puede facilitar la identificación de las columnas Order_Details.ProductID, Order_Details.UnitPrice y Order_Details.Quantity si quita los prefijos Order_DetailsYou can make the Order_Details.ProductID, Order_Details.UnitPrice and Order_Details.Quantity columns easier to identify by removing the Order_Details.de los nombres de columna.prefixes from the column names.Para cambiar el nombre de las columnas a ProductID, UnitPrice y Quantity, respectivamente:To rename the columns to ProductID, UnitPrice and Quantity, respectively:
- Haga doble clic en el encabezado de columna o manténgalo presionado, o bien haga clic con el botón derecho en el encabezado de columna y seleccione Cambiar nombre en el menú desplegable.Double-click or tap and hold each column header, or right-click the column header and select Rename from the dropdown.
- Elimine el prefijo Order_Details.Delete the Order_Details.de todos los nombres y luego presione ENTRAR.prefix from each name, and then press Enter.
Por último, para facilitar el acceso a la columna LineTotal, arrástrela y colóquela en la izquierda, justo a la derecha de la columna ShipCountry.Finally, to make the LineTotal column easier to access, drag and drop it to the left, just to the right of the ShipCountry column.
Revisión de los pasos de la consultaReview the query steps
A medida que dio forma a los datos y los transformó en el Editor de Power Query, cada paso se registró en el área Pasos aplicados del panel de configuración de consulta que se encuentra a la derecha del Editor de Power Query.As you shaped and transformed data in the Power Query Editor, each step was recorded in the Applied Steps area of the Query Settings pane on the right side of Power Query Editor.Puede retroceder en los pasos aplicados para revisar los cambios que realizó y editarlos, eliminarlos o reorganizarlos si es necesario, a pesar de que esto podría resultar un riesgo, porque cualquier cambio que se haga en los pasos anteriores podría afectar los pasos posteriores.You can step back through the Applied Steps to review the changes you made, and edit, delete, or rearrange them if necessary (although this can be risky, because changing preceding steps can break later steps).
Seleccione cada una de las consultas en la lista Consultas en el lado izquierdo del Editor de Power Query y revise los Pasos aplicados en Configuración de la consulta.Select each of your queries in the Queries list on the left side of Power Query Editor, and review the Applied Steps in Query Settings.Después de aplicar las transformaciones de datos anteriores, los pasos aplicados a las dos consultas deben ser similares a los siguientes:After applying the previous data transformations, the Applied Steps for your two queries should look like the following:
Sugerencia
Subyacentes a los pasos aplicados se encuentran las fórmulas escritas en el lenguaje de Power Query, que también se conoce como el lenguaje M.Underlying the Applied Steps are formulas written in the Power Query Language, also known as the M language.Para ver y editar las fórmulas, seleccione Editor avanzado en el grupo de consultas de la pestaña Inicio de la cinta de opciones.To see and edit the formulas, select Advanced Editor in the Query group of the Home tab of the ribbon.
Importación de las consultas transformadasImport the transformed queries
Cuando esté satisfecho con los datos transformados, seleccione Cerrar y aplicar > Cerrar y aplicar en el grupo Cerrar de la pestaña Inicio de la cinta de opciones, para importar los datos en la vista de informe de Power BI Desktop.When you are satisfied with your transformed data, select Close & Apply > Close & Apply in the Close group of the Home ribbon tab, to import the data into Power BI Desktop Report View.
Una vez cargados los datos, las consultas aparecen en la lista Campos de la vista de informe de Power BI Desktop.Once the data is loaded, the queries appear in the Fields list in the Power BI Desktop Report View .
Administración de la relación entre los conjuntos de datosManage the relationship between the datasets
Power BI Desktop no requiere combinar consultas para informar sobre estas.Power BI Desktop does not require you to combine queries to report on them.Sin embargo, puede usar las relaciones entre los conjuntos de datos, en función de los campos que tengan en común, para ampliar y enriquecer los informes.However, you can use the relationships between datasets, based on fields they have in common, to extend and enrich your reports.Power BI Desktop puede detectar automáticamente las relaciones, o puede crearlas en el cuadro de diálogo Administrar relaciones de Power BI Desktop.Power BI Desktop may detect relationships automatically, or you can create them in the Power BI Desktop Manage Relationships dialog.Para obtener más información sobre las relaciones en Power BI Desktop, vea Crear y administrar relaciones.For more details about relationships in Power BI Desktop, see Create and manage relationships.
Los conjuntos de datos de pedidos y productos de este tutorial comparten un campo ProductID común, por lo que existe una relación entre ellos basada en dicha columna.The Orders and Products datasets in this tutorial share a common ProductID field, so there is a relationship between them based on that column.
En la vista de informe de Power BI Desktop, seleccione Administrar relaciones en el área Relaciones de la pestaña Inicio de la cinta de opciones.In Power BI Desktop Report View, select Manage Relationships in the Relationships area of the Home ribbon tab.
En el cuadro de diálogo Administrar relaciones, observe que Power BI Desktop ya ha detectado y enumerado una relación activa entre las tablas de productos y pedidos.In the Manage Relationships dialog, note that Power BI Desktop has already detected and listed an active relationship between the Products and Orders tables.Para ver la relación, seleccione Editar.To view the relationship, select Edit.
Se abre el cuadro de diálogo Editar relación, donde se muestran los detalles sobre la relación.The Edit Relationship dialog opens, showing details about the relationship.
Power BI Desktop ha detectado automática y correctamente la relación; por tanto, puede seleccionar Cancelar y luego Cerrar para salir de los cuadros de diálogo de relaciones.Power BI Desktop has autodetected the relationship correctly, so you can select Cancel and then Close to exit the relationship dialogs.
También puede ver y administrar las relaciones entre las consultas si selecciona la vista Relación en el lado izquierdo de la ventana de Power BI Desktop.You can also view and manage the relationships between your queries by selecting the Relationship view at the left side of the Power BI Desktop window.Haga doble clic en la flecha situada en la línea que conecta las dos consultas para abrir el cuadro de diálogo Editar relación y ver o cambiar la relación.Double-click the arrow on the line connecting the two queries to open the Edit Relationship dialog and view or change the relationship.
Para volver a la vista de informe desde la vista de relaciones, seleccione el icono de Vista de informe.To get back to Report View from Relationships View, select the Report View icon.
Creación de visualizaciones con los datosCreate visualizations using your data
En la vista de informe de Power BI Desktop, puede crear una amplia gama de visualizaciones para obtener información a partir de los datos.In Power BI Desktop Report View, you can create a variety of visualizations to gain insights from your data.Puede generar informes con varias páginas y cada página puede tener varios objetos visuales.You can build reports with multiple pages, and each page can have multiple visuals.Tanto usted como otros usuarios pueden interactuar con las visualizaciones para ayudar a analizar y comprender los datos.You and others can interact with your visualizations to help analyze and understand your data.Para obtener más información sobre cómo ver y editar informes en el servicio Power BI (el sitio), consulte la información sobre la edición de un informe.For more information about viewing and editing reports in Power BI Service (your site), see Edit a Report.
Puede usar los dos conjuntos de datos y la relación entre ellos para facilitar la visualización y el análisis de los datos de ventas.You can use both of your data sets, and the relationship between them, to help visualize and analyze your sales data.
En primer lugar, cree un gráfico de columnas apiladas que utilice campos de las dos consultas para mostrar la cantidad de cada producto pedido.First, create a stacked column chart that uses fields from both queries to show the quantity of each product ordered.
Seleccione el campo Quantity de Orders en el panel Campos de la derecha, o bien arrástrelo hasta un espacio en blanco en el lienzo.Select the Quantity field from Orders in the Fields pane at the right, or drag it onto a blank space on the canvas.De esta forma se crea un gráfico de columnas apiladas en el que se muestra la cantidad total de todos los productos pedidos.This creates a stacked column chart showing the total quantity of all products ordered.
Seleccione ProductName en Products en el panel Campos, o bien arrástrelo hasta el gráfico, para mostrar la cantidad de cada producto pedido.Select ProductName from Products in the Fields pane, or drag it onto the chart, to show the quantity of each product ordered.
Para ordenar los productos según los más pedidos o los menos pedidos, seleccione los puntos suspensivos Más opciones ( ... ) en la parte superior derecha de la visualización y luego seleccione Ordenar por cantidad.To sort the products by most to least ordered, select the More options ellipsis (...) at the upper right of the visualization, and then select Sort By Quantity.
Use los controladores de las esquinas del gráfico para ampliarlo, para que aparezcan más nombres de productos.Use the handles at the corners of the chart to enlarge it so more product names are visible.
A continuación, cree un gráfico en el que aparezcan los importes en dólares de los pedidos (LineTotal) a lo largo del tiempo (OrderDate).Next, create a chart showing order dollar amounts (LineTotal) over time (OrderDate).
Sin ningún elemento seleccionado en el lienzo, seleccione LineTotal en Orders en el panel Campos, o bien arrástrelo hasta un espacio en blanco en el lienzo.With nothing selected on the canvas, select LineTotal from Orders in the Fields pane, or drag it to a blank space on the canvas.En el gráfico de columnas apiladas se muestra la cantidad total en dólares de todos los pedidos.The stacked column chart shows the total dollar amount of all orders.
Con el gráfico seleccionado, seleccione OrderDate en Orders, o bien arrástrelo hasta el gráfico.With the chart selected, select OrderDate from Orders, or drag it onto the chart.En el gráfico ahora se muestran el total de línea de cada fecha de pedido.The chart now shows line totals for each order date.
Para cambiar el tamaño de la visualización y poder ver más datos, arrastre las esquinas.Resize the visualization by dragging the corners to be able to see more data.
Sugerencia
Si solo ve los años en el gráfico (solo tres puntos de datos), despliegue la flecha que está junto a OrderDate en el campo Eje del panel Visualizaciones y seleccione OrderDate en lugar de Jerarquía de fechas.If you only see Years on the chart (only three data points), drop down the arrow next to OrderDate in the Axis field of the Visualizations pane, and select OrderDate instead of Date Hierarchy.
Por último, cree una visualización de mapa en la que se muestren las cantidades de pedidos de cada país.Finally, create a map visualization showing order amounts from each country.
Sin ningún elemento seleccionado en el lienzo, seleccione ShipCountry en Orders en el panel Campos, o bien arrástrelo hasta un espacio en blanco en el lienzo.With nothing selected on the canvas, select ShipCountry from Orders in the Fields pane, or drag it to a blank space on the canvas.Power BI Desktop detecta que los datos se corresponden con los nombres de país y crea automáticamente una visualización de mapa, con un punto de datos para cada país que había realizado pedidos.Power BI Desktop detects that the data is country names, and automatically creates a map visualization, with a data point for each country that had orders.
Para que los tamaños de los puntos de datos reflejen las cantidades de pedidos de cada país, arrastre el campo LineTotal al mapa, o bien arrástrelo hasta Arrastrar campos de datos aquí en Tamaño, en la mitad inferior del panel Visualizaciones.To make the sizes of the data points reflect the order amounts for each country, drag the LineTotal field onto the map (or drag it to Drag data fields here under Size, in the bottom half of the Visualizations pane).Los tamaños de los círculos en el mapa ahora reflejan las cantidades en dólares de los pedidos de cada país.The sizes of the circles on the map now reflect the dollar amounts of the orders from each country.
Interactuación con los objetos visuales de informes para analizar másInteract with your report visuals to analyze further
Power BI Desktop permite descubrir las futuras tendencias al interactuar con objetos visuales que se filtran y resaltan entre sí.Power BI Desktop lets you uncover further trends by interacting with visuals that cross-highlight and filter each other.Para más información, consulte Filtrado y resaltado en informes.For more information, see Filtering and Highlighting in Reports.
Debido a la relación que existe entre las consultas, las interacciones con una visualización afectarán a todas las demás visualizaciones de la página.Because of the relationship between your queries, interactions with one visualization will affect all the other visualizations on the page.
En la visualización de mapa, seleccione el círculo en el centro de Canadá.On the map visualization, select the circle centered in Canada.Tenga en cuenta que se filtran las otras dos visualizaciones para resaltar los totales de línea y las cantidades de pedidos solo para Canadá.Note that the other two visualizations filter to highlight the line totals and order quantities just for Canada.
Si selecciona uno de los productos en el gráfico Cantidad por ProductName, el mapa y el gráfico de fechas se filtra para reflejar los datos de dicho producto y, si selecciona una de las fechas en el gráfico LineTotal por OrderDate, el mapa y gráfico de productos se filtran para mostrar los datos de dicha fecha.If you select one of the products in the Quantity by ProductName chart, the map and the date chart filter to reflect the data for that product, and if you select one of the dates in the LineTotal by OrderDate chart, the map and the product chart filter to show the data for that date.
Sugerencia
Para anular una selección, vuelva a realizar la selección o seleccione alguna de las otras visualizaciones.To deselect a selection, select it again, or select one of the other visualizations.
Completar el informe de análisis de ventasComplete the sales analysis report
El informe completo combina los datos del archivo de Excel Products.xlsx y la fuente de OData de Northwind en objetos visuales que le ayudarán a analizar la información de pedidos de distintos países, períodos de tiempo y productos.Your completed report combines data from the Products.xlsx Excel file and the Northwind OData feed in visuals that help analyze order information for different countries, timeframes, and products.Cuando el informe esté listo, puede cargarlo en el servicio Power BI para compartirlo con otros usuarios de Power BI.When your report is ready, you can upload it to Power BI service to share it with other Power BI users.
Pasos siguientesNext steps
Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.
Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on conditions (or criteria). If the condition is true, the cell range is formatted based on that condition; if the condition is false, the cell range is not formatted based on that condition.
You can sort and filter by format, including cell background color and font color, whether you have manually or conditionally formatted the cells. The following example shows conditional formatting that uses cell background colors, a 3 arrow icon set, and data bars.
WindowsOnline
Apply conditional formatting to a cell range, Excel table or a PivotTable
You can apply conditional formatting to a cell range, an Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.
Note: When you create a conditional format, you can reference only other cells on the same worksheet or, in certain cases, cells on worksheets in the same currently open workbook. You can't use conditional formatting on external references to another workbook.
Conditional formatting for a PivotTable report
Conditional formatting in a PivotTable report is different from conditional formatting in a cell range or an Excel table in several ways:
If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.
The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.
Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.
There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.
The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box.
Method | Use this method if you want to select |
Scoping by selection |
|
Scoping by value field |
|
Scoping by corresponding field |
When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:
|
Apply conditional formatting with Quick Analysis
Note: Quick Analysis is not available in Excel 2010 and previous versions.
Use the Quick Analysis button to apply selected conditional formatting to the selected data. The Quick Analysis button appears automatically when you select data in your spreadsheet.
Select the data in your spreadsheet. The Quick Analysis button appears on the lower-right corner of the selection.
Click the Quick Analysis button , or press Ctrl+Q.
Then, on the Formatting tab, move your mouse over the different options to see a Live Preview on your data, and then click on a formatting option.
Notes:
The formatting options that appear in the Formatting tab will depend on the data you have selected. If your selection contains only text, then the available options are Text, Duplicate, Unique, Equal To, and Clear. When the selection contains only numbers, or both text and numbers, then the options are Data Bars, Colors, Icon Sets, Greater, Top 10%, and Clear.
Live preview will only render for those formatting options that can be used on your data. For example, if your selected cells don't contain matching data and you select Duplicate, the live preview will not work.
If the Text that Contains dialog box appears, enter the formatting option you want to apply and click OK.
Use a formula to determine which cells to format
If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.
The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Selected cells.
Corresponding field: Click All cells showing <Values field> values.
Value field: Click All cells showing <Values field> for <Row>.
Under Select a Rule Type, click Use a formula to determine which cells to format.
Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula.
You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).Click Format to display the Format Cells dialog box.
Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.
You can choose more than one format. The formats you select are shown in the Preview box.Example 1: Use two conditional formats with criteria that uses AND and OR tests
In the example shown here, the first rule formats two cells green if both conditions are true. If the result of that test is not True, the second rule formats two cells red if either of the conditions are False.
A home buyer has budgeted up to $75,000 as a down payment and $1,500 per month as a mortgage payment. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green.
If either the down payment or the monthly payment meet the buyer's budget, B4 and B5 are formatted red. Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells.
Formula for first rule (applies green color)
AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))
Formula for second rule (applies red color)
=OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))
Example 2: Shade every other row by using the MOD and ROW functions
A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color. You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted. The rule uses this formula: =MOD(ROW(),2)=1.
Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references.
For more information on using formulas in conditional formatting, see Use a formula to apply conditional formatting.
Format all cells by using a two-color scale
Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, as shown below, you can specify that higher value cells have a more green color and lower value cells have a more yellow color.
Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value other than an error value.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.
Select a two-color scale.
Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Selected cells.
All cells for a Value label: Click All cells showing <Value label> values.
All cells for a Value label, excluding subtotals and the grand total: Click All cells showing <Value label> values for <Row Label>.
Under Select a Rule Type, click Format all cells based on their values (default).
Under Edit the Rule Description, in the Format Style list box, select 2-Color Scale.
To select a type in the Type box for Minimum and Maximum, do one of the following:
Format lowest and highest values: Select Lowest Value and Highest Value.
In this case, you do not enter a Minimum and MaximumValue.
Format a number, date, or time value: Select Number and then enter a Minimum and MaximumValue.
Format a percentage Percent: Enter a Minimum and MaximumValue.
Valid values are from 0 (zero) to 100. Don't enter a percent sign.
Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.
Format a percentile: Select Percentile and then enter a Minimum and MaximumValue. Valid percentiles are from 0 (zero) to 100.
Use a percentile when you want to visualize a group of high values (such as the top 20thpercentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.
Format a formula result: Select Formula and then enter values for Minimum and Maximum.
The formula must return a number, date, or time value.
Start the formula with an equal sign (=).
Invalid formulas result in no formatting being applied.
It's a good idea to test the formula to make sure that it doesn't return an error value.
Notes:
Make sure that the Minimum value is less than the Maximum value.
You can choose a different type for the Minimum and Maximum. For example, you can choose a number for Minimum a percentage for Maximum.
To choose a Minimum and Maximum color scale, click Color for each, and then select a color.
If you want to choose additional colors or create a custom color, click More Colors. The color scale you select is shown in the Preview box.
Format all cells by using a three-color scale
Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.
Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value that you specify (such as 0, or 'N/A') instead of an error value.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.
Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.
Hover over the color scale icons to see which icon is a three-color scale.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied..
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select 3-Color Scale.
Select a type for Minimum, Midpoint, and Maximum. Do one of the following:
Format lowest and highest values: Select a Midpoint.
In this case, you do not enter a Lowest and HighestValue.
Format a number, date, or time value: Select Number and then enter a value for Minimum, Midpoint, and Maximum.
Format a percentage: Select Percent and then enter a value for Minimum, Midpoint, and Maximum. Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.
Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.
Format a percentile: Select Percentile and then enter a value for Minimum, Midpoint, and Maximum.
Valid percentiles are from 0 (zero) to 100.
Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.
Format a formula result: Select Formula and then enter a value for Minimum, Midpoint, and Maximum.
The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.
Notes:
You can set minimum, midpoint, and maximum values for the range of cells. Make sure that the value in Minimum is less than the value in Midpoint, which in turn is less than the value in Maximum.
You can choose a different type for Minimum, Midpoint, and Maximum. For example, you can choose a Minimum number, Midpoint percentile, and Maximum percent.
In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.
To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color.
To choose additional colors or create a custom color, click More Colors.
The color scale you select is shown in the Preview box.
Format all cells by using data bars
A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.
The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, and stretches to the left for negative values.
Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value (such as 0 or 'N/A') instead of an error value.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select Data Bar.
Select a Minimum and MaximumType. Do one of the following:
Format lowest and highest values: Select Lowest Value and Highest Value.
In this case, you do not enter a value for Minimum and Maximum.
Format a number, date, or time value: Select Number and then enter a Minimum and MaximumValue.
Format a percentage: Select Percent and then enter a value for Minimum and Maximum.
Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.
Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.
Format a percentile Select Percentile and then enter a value for Minimum and Maximum.
Valid percentiles are from 0 (zero) to 100.
Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.
Format a formula result Select Formula, and then enter a value for Minimum and Maximum.
The formula has to return a number, date, or time value.
Start the formula with an equal sign (=).
Invalid formulas result in no formatting being applied.
It's a good idea to test the formula to make sure that it doesn't return an error value.
Notes:
Make sure that the Minimum value is less than the Maximum value.
You can choose a different type for Minimum and Maximum. For example, you can choose a Minimum number and a Maximum percent.
To choose a Minimum and Maximum color scale, click Bar Color.
If you want to choose additional colors or create a custom color, click More Colors. The bar color you select is shown in the Preview box.
To show only the data bar and not the value in the cell, select Show Bar Only.
To apply a solid border to data bars, select Solid Border in the Border list box and choose a color for the border.
To choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box.
To format negative bars, click Negative Value and Axis and then, in the Negative Value and Axis Settings dialog box, choose options for the negative bar fill and border colors. You can choose position settings and a color for the axis. When you are finished selecting options, click OK.
You can change the direction of bars by choosing a setting in the Bar Direction list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data.
Format all cells by using an icon set
Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.
The example shown here works with several examples of conditional formatting icon sets.
You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it. To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions. You can also create your own combination of icon sets; for example, a green 'symbol' check mark, a yellow 'traffic light', and a red 'flag.'
Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value (such as 0 or 'N/A') instead of an error value.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select Icon Set.
Select an icon set. The default is 3 Traffic Lights (Unrimmed). The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set.
You can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.
Do one of the following:
Format a number, date, or time value: Select Number.
Format a percentage: Select Percent.
Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.
Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.
Format a percentile: Select Percentile. Valid percentiles are from 0 (zero) to 100.
Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) using a particular icon and low values (such as the bottom 20th percentile) using another icon, because they represent extreme values that might skew the visualization of your data.
Format a formula result: Select Formula, and then enter a formula in each Value box.
The formula must return a number, date, or time value.
Start the formula with an equal sign (=).
Invalid formulas result in no formatting being applied.
It's a good idea to test the formula to make sure that it doesn't return an error value.
To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order.
To show only the icon and not the value in the cell, select Show Icon Only.
Notes:
You may need to adjust the column width to accommodate the icon.
The size of the icon shown depends on the font size that is used in that cell. As the size of the font is increased, the size of the icon increases proportionally.
Format only cells that contain text, number, or date or time values
To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to 'SouthEast.'
The examples shown here work with examples of built-in conditional formatting criteria, such as Greater Than, and Top %. This formats cities with a population greater than 2,000,000 with a green background and average high temperatures in the top 30% with orange.
Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or date, only by number.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.
Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.
Enter the values you want to use, and then select a format.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or on other worksheets, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format only cells that contain.
Under Edit the Rule Description, in the Format only cells with list box, do one of the following:
Format by number, date, or time: Select Cell Value, select a comparison operator, and then enter a number, date, or time.
For example, select Between and then enter 100 and 200, or select Equal to and then enter 1/1/2009.
You can also enter a formula that returns a number, date, or time value.
If you enter a formula, start it with an equal sign (=).
Invalid formulas result in no formatting being applied
It's a good idea to test the formula to make sure that it doesn't return an error value.
Format by text: Select Specific Text, choosing a comparison operator, and then enter text.
For example, select Contains and then enter Silver, or select Starting with and then enter Tri.
Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is 255 characters.
You can also enter a formula that returns text.
If you enter a formula, start it with an equal sign (=).
Invalid formulas result in no formatting being applied.
It's a good idea to test the formula to make sure that it doesn't return an error value.
Format by date: Select Dates Occurring and then select a date comparison.
For example, select Yesterday or Next week.
Format cells with blanks or no blanks: Select Blanks or No Blanks.
A blank value is a cell that contains no data and is different from a cell that contains one or more spaces (spaces are considered as text).
Format cells with error or no error values: Select Errors or No Errors.
Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.
To specify a format, click Format. The Format Cells dialog box appears.
Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.
You can choose more than one format. The formats you select are shown in the Preview box.
Punto De Venta En Excel
Format only top or bottom ranked values
You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department .
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.
Select the command you want, such as Top 10 items or Bottom 10 %.
Enter the values you want to use, and then select a format.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format only top or bottom ranked values.
Under Edit the Rule Description, in the Format values that rank in the list box, select Top or Bottom.
Do one of the following:
To specify a top or bottom number, enter a number and then clear the % of the selected range box. Valid values are 1 to 1000.
To specify a top or bottom percentage, enter a number and then select the % of the selected range box. Valid values are 1 to 100.
Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.
By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:
A column and its parent row field, by selecting each Column group.
A row and its parent column field, by selecting each Row group.
To specify a format, click Format. The Format Cells dialog box appears.
Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.
You can choose more than one format. The formats you select are shown in the Preview box.
Format only values that are above or below average
You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.
Select the command you want, such as Above Average or Below Average.
Enter the values you want to use, and then select a format.
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
Selection: Click Just these cells.
Corresponding field: Click All <value field> cells with the same fields.
Value field: Click All <value field> cells.
Under Select a Rule Type, click Format only values that are above or below average.
Under Edit the Rule Description, in the Format values that are list box, do one of the following:
To format cells that are above or below the average for all of the cells in the range, select Above or Below.
To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.
Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.
By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:
A column and its parent row field, by selecting each Column group.
A row and its parent column field, by selecting each Row group.
Click Format to display the Format Cells dialog box.
Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.
You can choose more than one format. The formats you select are displayed in the Preview box.
Format only unique or duplicate values
Note: You can't conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.
In the example shown here, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class (duplicate instructor names are highlighted in a rose color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.
Select Duplicate Values.
Enter the values you want to use, and then select a format.
Advanced formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.
Do one of the following:
To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
To change a conditional format, do the following:
Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.
Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.
Under Select a Rule Type, click Format only unique or duplicate values.
Under Edit the Rule Description, in the Format all list box, select unique or duplicate.
Click Format to display the Format Cells dialog box.
Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.
You can choose more than one format. The formats you select are shown in the Preview box.
Copy and paste conditional formatting
If you want to apply an existing formatting style to new or other data on your worksheet, you can use Format Painter to copy the conditional formatting to that data.
Click the cell that has the conditional formatting you want to copy.
Click Home > Format Painter.
The pointer changes to a paintbrush.
Tip: You can double-click Format Painter if you want to keep using the paintbrush to paste the conditional formatting in other cells.
To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
To stop using the paintbrush, press Esc.
Note: If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust relative and absolute references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references.
Find cells that have conditional formatting
If your worksheet has one or more cells with a conditional format, you can quickly locate the cells so that you can copy, change, or delete the conditional formats. You can use the Go To Special command to either find only cells with a specific conditional format or find all cells with conditional formats.
Find all cells that have a conditional format
Click any cell that does not have a conditional format.
On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Conditional Formatting.
Find only cells that have the same conditional format
Click any cell that has the conditional format that you want to find.
On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.
Click Conditional formats.
Click Same under Data validation.
Clear conditional formatting
Clear conditional formatting on a worksheet
On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
Clear conditional formatting in a range, table, or PivotTable
Select the range of cells, table, or PivotTable for which you want to clear conditional formats.
On the Home tab, click Conditional Formatting > Clear Rules.
Depending on what you have selected, click Selected Cells, This Table, or This PivotTable.
Format all cells by using a color scale
Color scales are visual guides that help you understand data distribution and variation. There are two kinds of color scales—two-color scales and three-color scales.
A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, you can specify that higher value cells have a more green color and lower value cells have a more yellow color.
A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.
Select one or more cells in a range, table, or a PivotTable.
Click Home > Tables > Conditional Formatting > Color Scales and then select a color scale.
Format all cells by using data bars
A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.
Select one or more cells in a range, table, or a PivotTable.
Click Home > Tables > Conditional Formatting > Data Bars and then select a data bar.
Format all cells by using an icon set
Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.
Select one or more cells in a range, table, or a PivotTable.
Click Home > Tables > Conditional Formatting > Icon Sets and then select an icon set.
Format only cells that contain text, number, or date or time values
To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to 'SouthEast.'
Select one or more cells in a range, table, or a PivotTable.
On the Home tab, in the Tables group, click Highlight Cell Rules.
Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.
Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK.
Format only top or bottom ranked values
You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department.
Select one or more cells in a range, table, or a PivotTable.
On the Home tab, in the Tables group, click Top/Bottom Rules.
Select the command you want, such as Top 10 items or Bottom 10 %.
Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK.
Format only values that are above or below average
You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.
Select one or more cells in a range, table, or a PivotTable.
On the Home tab, in the Tables group, click Top/Bottom Rules.
Select the command you want, such as Above Average or Below Average.
Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK.
Format only unique or duplicate values
You can find unique or duplicate values in a range of cells.
Select one or more cells in a range, table, or a PivotTable.
On the Home tab, in the Tables group, click Highlight Cell Rules, and then select Duplicate Values.
Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK.
Copy and paste conditional formatting
There are two ways to copy and paste conditional formatting:
Use Format Painter to copy conditional formatting to other data.
Copy the formatting in conditionally formatted cells and paste to other cells. To do that:
Select the cells with the conditional formatting you want to copy to other cells.
Right-click the selection > Copy, or press Ctrl+C.
Select the first cell in the destination area where you want to paste the conditional formatting.
Note: Because you want to paste the formatting only, don't right-click > Paste, or press Ctrl+V. Also, if you've copied data that's in a different number format from the data in the destination area, make sure you adjust the number format in the destination area after you paste the formatting.
Clear conditional formatting
You can clear conditional formatting in selected cells or the entire worksheet.
To clear conditional formatting in selected cells:
Select the cells in the worksheet.
Click the Home tab > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
To clear conditional formatting in the entire worksheet, click the Home tab > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
Do you have a specific function question?
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.