Unable to position custom data labels in the center of Google Sheets cascading chart

The context

I've created a cascading chart stacked in Google Sheets, with custom data tags (guided by Ben Collin's tutorial on custom data labels).

My goal is to place the custom data label at the center of each segment of the stack.

When using the Value label type, the labels are positioned correctly using Center (Example 1 below).


When I change the type of custom label, the positioning becomes non-centered (example 2 below).

I have tested the following behavior when using custom labels:

  • The problem persists when selecting other types of items. The Center option is not unique
  • The problem persists when all segment values ​​are positive (see Example 3 below).


Is this a known problem with custom data labels? Are there any known fixes / workarounds?

Or should I use the addition of manually positioned text boxes on segments of the stack for custom, centered data labels?

Example 1 – Value tags & # 39; Are correctly positioned in the center
Value labels placed in the center

Example 2 – "Custom" labels are incorrectly positioned

Improperly positioned custom labels

Example 3 – The problem persists when all segment values ​​are positive. The custom labels below are not centered.
Example 3 - all positive values

google sheets – T-Shirts Marvel Comics Thor Ragnarok Cast

Buy your Marvel Comics Thor Ragnarok t-shirts. If you are a true admirer of a gorgeous film about a studio or an American superhero based on the Marvel Comics superhero team, you must take care of yourself with the appearance of Thor Ragnarok T-shirts. shirt only for you.

You can get your Thor ragnarok t-shirt now:

Take Now Avengers Marvel Comics Thor Ragnarok Cast T-Shirts:




google sheets – How to count with dates before the date of today which is automatically updated

The first thing to keep in mind is that Google Sheets (as well as other spreadsheet applications) use serial numbers to handle dates. So, when a value looks like 8/15/2019 (if managed as a date and not as a text) Google Sheets internally handles this value as 43692.

Recalling COUNTIF, this function requires two arguments and if you will use a comparison operator as < it should be the quote attached.

The formula to use is:

=COUNTIF(A2:A32, "<"& C13)


Google Sheets automatically adding more than 20,000 blank lines

Trying to expand a Google sheet as many times as I want, but for some reason and without any explanation, the sheet suddenly adds up to 25,000 rows while I'm still only in line 600 Is there a way to prevent this feature? Since the beginning of this operation, the page has begun to shift very slowly to the point that a complicated conditional formatting configuration takes a few minutes to complete on new cells.

Google Sheets – Summing Numbers on a Spreadsheet Based on the Column Header

I am looking to automate our timesheet process. Each staff member has their own separate Google spreadsheet, which contains the dates in a column, and then a variable number of named columns based on the different projects they are working on.

I want to create an automatic summary of the total number of hours per project per staff member in another Google sheet. Like that:

enter the description of the image here

I know that I can use importRange to extract numbers of specific cells in several different workbooks. But I struggle to find a way to sum hours per project without each staff member having more than 60 projects on their timesheet and keeping them in the same order so that cell references never change and break not the formulas.

I want a formula that adds numbers to a column IF the header of that column is a specific name, for example. = Project 1.

So, the range of the formula would cover a complete worksheet, but would only give one column based on the header.


Open the Gmail compose window in the same window if you clicked Google Sheets to reply to the comment?

Below is an example of e-mail from comments from Google Sheets.

If I click on "Reply", the composition window opens in a new browser tab.

Is there a way to open this compose window in the same browser tab?

enter the description of the image here

google sheets – Create a table showing the difference between the values ​​of two pivot tables

Our bookstore has two separate documents in Google Sheets (one containing the purchase data and the other containing the sales data). Each document contains a sheet with a pivot table that summarizes the total purchases / sales by book category. I wanted to create a third sheet that shows the difference between the two (ie the profit margin by category).

I've been able to find the IMPORTRANGE function that can extract the data range from one sheet to another, but I do not see how to generate a new PivotTable showing the difference between them. two paintings.

Can someone explain me how to do this? (Suppose, for the sake of simplicity, that the two documents are named "A" and "B" and that the data is in a sheet called "Sheet 1" in each of them, each with two columns "Section" and "Total").

google sheets – Extract specific phrases from one cell and pass them on to another

The functions you are looking for are REGEXMATCH combined with OR and IF.
So, IF will test three conditions (with OR) and then if OR returns a TRUE boolean, copy the contents of the cell, otherwise do what you want.


Here's a way to do it with one formula


Synchronize Google Sheets with Google Calendar

Automatically add a calendar of Google Sheets to the calendar

I followed the instructions in this video:

and it works well. However, the video does not include capturing and synchronizing the description of the event.

Here is the script below:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
  .addItem('Schedule shifts now', 'scheduleShifts')

function scheduleShifts() {

  Task 1) Open the Event Calendar.

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("C4").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);

  Task 2) Pull each shift information into the code, in a form that the code can understand.

  var signups = spreadsheet.getRange("A8:C").getValues();

  Task 3) Do the Work!

  for (x=0; x < signups.length; x++) {

    var shift = signups(x);

    var startTime = shift(1);
    var endTime = shift(2);
    var volunteer = shift(0);
    var des = shift(3)

    eventCal.createEvent(volunteer, startTime, endTime, des);

Reference table data in Google Sheets

In Google Docs Help> Using Tables in Google Sheets:

Any function taking a range (for example, A1: B6) as input parameter
will also accept a table in its place. For example, SPARKLINE takes a
range as the first parameter to specify the values ​​to be plotted.

I'm trying to reference a cell that contains array values ​​to use as data parameter for a sparkline. My incoming data is in format, for example. {43;12;98;2} and say that it is placed in cell A1.

The direct inclusion of values ​​works very well: =sparkline({43;12;98;2})

But that fails: =sparkline(A1) like removing the braces from A1 and doing it =sparkline({A1})

So, how can we refer to another cell with an array of values?